package cn.iocoder.yudao.module.system.service.examscore;

import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.excel.core.util.ExcelUtils;
import cn.iocoder.yudao.module.infra.api.file.FileApi;
import cn.iocoder.yudao.framework.security.core.util.SecurityFrameworkUtils;
import cn.iocoder.yudao.module.system.controller.admin.examscore.vo.*;
import cn.iocoder.yudao.module.system.controller.admin.examtest.vo.ExamTestCreateReqVO;
import cn.iocoder.yudao.module.system.convert.examscore.ExamScoreConvert;
import cn.iocoder.yudao.module.system.dal.dataobject.examclass.ExamClassDO;
import cn.iocoder.yudao.module.system.dal.dataobject.examclass.ExamClassStudentDO;
import cn.iocoder.yudao.module.system.dal.dataobject.examscore.ExamScoreDO;
import cn.iocoder.yudao.module.system.dal.dataobject.examtest.ExamTestDO;
import cn.iocoder.yudao.module.system.dal.dataobject.subjectscoredetail.SubjectScoreDetailDO;
import cn.iocoder.yudao.module.system.dal.dataobject.topscorerecord.TopScoreRecordDO;
import cn.iocoder.yudao.module.system.dal.mysql.examclass.ExamClassMapper;
import cn.iocoder.yudao.module.system.dal.mysql.examclass.ExamClassStudentMapper;
import cn.iocoder.yudao.module.system.dal.mysql.examscore.ExamScoreMapper;
import cn.iocoder.yudao.module.system.dal.mysql.subjectscoredetail.SubjectScoreDetailMapper;
import cn.iocoder.yudao.module.system.dal.mysql.topscorerecord.TopScoreRecordMapper;
import cn.iocoder.yudao.module.system.dal.mysql.equivalentscorerank.EquivalentScoreRankMapper;
import cn.iocoder.yudao.module.system.dal.mysql.equivalentscorerank.EquivalentScoreDetailMapper;
import cn.iocoder.yudao.module.system.dal.dataobject.equivalentscorerank.EquivalentScoreRankDO;
import cn.iocoder.yudao.module.system.enums.ErrorCodeConstants;
import cn.iocoder.yudao.module.system.framework.examscore.template.ExamScoreTemplateManager;
import cn.iocoder.yudao.module.system.service.autogeneration.AutoGenerationService;
import cn.iocoder.yudao.module.system.service.examtest.ExamTestService;
import cn.iocoder.yudao.module.system.service.ranking.RankingCalculationService;
import cn.iocoder.yudao.module.system.service.scoreassignment.ScoreAssignmentService;
import cn.iocoder.yudao.module.system.service.subjectcombination.SubjectCombinationService;
import cn.iocoder.yudao.module.system.enums.DuplicateHandleStrategyEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.validation.Valid;
import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

import static cn.iocoder.yudao.framework.common.exception.util.ServiceExceptionUtil.exception;
import static cn.iocoder.yudao.module.system.enums.ErrorCodeConstants.*;

/**
 * 学生成绩 Service 实现类
 *
 * @author 芋道源码
 */
@Service
@Validated
@Slf4j
public class ExamScoreServiceImpl implements ExamScoreService {

    @Resource
    private ExamScoreMapper examScoreMapper;
    
    @Resource
    private ExamClassMapper examClassMapper;
    
    @Resource
    private ExamClassStudentMapper examClassStudentMapper;
    
    @Resource
    private SubjectScoreDetailMapper subjectScoreDetailMapper;
    
    @Resource
    private TopScoreRecordMapper topScoreRecordMapper;

    @Resource
    private EquivalentScoreRankMapper equivalentScoreRankMapper;

    @Resource
    private EquivalentScoreDetailMapper equivalentScoreDetailMapper;

    @Resource
    private ExamScoreTemplateManager examScoreTemplateManager;
    
    @Resource
    private ExamTestService examTestService;
    
    @Resource
    private FileApi fileApi;
    
    @Resource
    private AutoGenerationService autoGenerationService;
    
    @Resource
    private SubjectCombinationService subjectCombinationService;
    
    @Resource
    private ScoreAssignmentService scoreAssignmentService;
    
    @Resource
    private RankingCalculationService rankingCalculationService;
    
    @Resource
    private ExcelFormatDetector excelFormatDetector;
    
    @Resource
    private DynamicHeaderParser dynamicHeaderParser;
    
    @Resource
    private DynamicDataRowParser dynamicDataRowParser;

    @Resource
    @org.springframework.context.annotation.Lazy
    private cn.iocoder.yudao.module.system.service.equivalentscorerank.EquivalentScoreRankService equivalentScoreRankService;

    @Resource
    @org.springframework.context.annotation.Lazy
    private cn.iocoder.yudao.module.system.service.gugu.ProvinceScoreService provinceScoreService;

    @Resource
    @org.springframework.context.annotation.Lazy
    private cn.iocoder.yudao.module.system.service.examcontrolline.ExamControlLineService examControlLineService;

    @Override
    public Long createExamScore(@Valid ExamScoreCreateReqVO createReqVO) {
        // 校验考试和学生是否已存在成绩
        validateExamScoreExists(createReqVO.getExamId(), createReqVO.getStudentId(), null);
        
        // 插入
        ExamScoreDO examScore = ExamScoreConvert.INSTANCE.convert(createReqVO);
        
        // 设置部门ID为当前登录用户的部门ID（用于数据权限控制）
        Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
        if (currentUserDeptId != null) {
            examScore.setDeptId(currentUserDeptId);
            log.debug("设置成绩记录部门ID为登录用户部门: {}, 学生: {}", 
                     currentUserDeptId, createReqVO.getStudentName());
        } else {
            log.warn("当前登录用户未设置部门ID，成绩记录将不设置部门信息");
        }
        
        examScoreMapper.insert(examScore);
        
        // 重新计算排名
        calculateRanking(createReqVO.getExamId());
        
        // 返回
        return examScore.getId();
    }
    
    /**
     * 合并多个sheet的导入结果
     */
    private void mergeImportResults(ExamScoreImportRespVO finalResult, ExamScoreImportRespVO sheetResult, String sheetName) {
        // 合并创建的用户名列表
        if (sheetResult.getCreateUsernames() != null) {
            for (String username : sheetResult.getCreateUsernames()) {
                finalResult.getCreateUsernames().add("[" + sheetName + "] " + username);
            }
        }
        
        // 合并更新的用户名列表
        if (sheetResult.getUpdateUsernames() != null) {
            for (String username : sheetResult.getUpdateUsernames()) {
                finalResult.getUpdateUsernames().add("[" + sheetName + "] " + username);
            }
        }
        
        // 合并重复的用户名列表
        if (sheetResult.getDuplicateUsernames() != null) {
            for (String username : sheetResult.getDuplicateUsernames()) {
                finalResult.getDuplicateUsernames().add("[" + sheetName + "] " + username);
            }
        }
        
        // 合并失败的用户名映射
        if (sheetResult.getFailureUsernames() != null) {
            for (Map.Entry<String, String> entry : sheetResult.getFailureUsernames().entrySet()) {
                finalResult.getFailureUsernames().put("[" + sheetName + "] " + entry.getKey(), entry.getValue());
            }
        }
    }

    @Override
    public void updateExamScore(@Valid ExamScoreUpdateReqVO updateReqVO) {
        // 校验存在
        validateExamScoreExists(updateReqVO.getId());
        
        // 更新
        ExamScoreDO updateObj = ExamScoreConvert.INSTANCE.convert(updateReqVO);
        
        // 设置部门ID为当前登录用户的部门ID（用于数据权限控制）
        Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
        if (currentUserDeptId != null) {
            updateObj.setDeptId(currentUserDeptId);
            log.debug("更新成绩记录部门ID为登录用户部门: {}, 学生: {}", 
                     currentUserDeptId, updateReqVO.getStudentName());
        } else {
            log.warn("当前登录用户未设置部门ID，成绩记录将不设置部门信息");
        }
        
        examScoreMapper.updateById(updateObj);
        
        // 重新计算排名
        ExamScoreDO examScore = examScoreMapper.selectById(updateReqVO.getId());
        if (examScore != null) {
            calculateRanking(examScore.getExamId());
        }
    }

    @Override
    public void deleteExamScore(Long id) {
        // 校验存在
        ExamScoreDO examScore = validateExamScoreExists(id);
        
        // 删除
        examScoreMapper.deleteById(id);
        
        // 重新计算排名
        calculateRanking(examScore.getExamId());
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int deleteExamScoresByExamId(Long examId) {
        // 校验考试是否存在
        if (examId == null) {
            throw exception(ErrorCodeConstants.EXAM_NOT_EXISTS);
        }
        
        // 查询该考试下的成绩数量
        Long count = examScoreMapper.selectCountByExamId(examId);
        if (count == 0) {
            log.info("考试ID {} 下没有成绩数据，无需删除", examId);
            return 0;
        }
        
        log.info("开始清除考试ID为 {} 的导入数据，共有 {} 条成绩记录", examId, count);
        
        int totalDeleted = 0;
        
        // 1. 删除等效分详情表数据（需要先查询 equivalent_score_rank 的 ID）
        List<EquivalentScoreRankDO> equivalentScoreRanks = equivalentScoreRankMapper.selectByExamId(examId);
        if (!equivalentScoreRanks.isEmpty()) {
            List<Long> equivalentScoreRankIds = equivalentScoreRanks.stream()
                    .map(EquivalentScoreRankDO::getId)
                    .collect(Collectors.toList());
            int deletedEquivalentScoreDetails = equivalentScoreDetailMapper.deleteByEquivalentScoreRankIds(equivalentScoreRankIds);
            log.info("删除等效分详情数据 {} 条", deletedEquivalentScoreDetails);
            totalDeleted += deletedEquivalentScoreDetails;
        }
        
        // 2. 删除等效分排名表数据
        int deletedEquivalentScoreRanks = equivalentScoreRankMapper.deleteByExamId(examId);
        log.info("删除等效分排名数据 {} 条", deletedEquivalentScoreRanks);
        totalDeleted += deletedEquivalentScoreRanks;
        
        // 3. 删除科目成绩详情表数据
        int deletedSubjectScoreDetails = subjectScoreDetailMapper.deleteByExamId(examId);
        log.info("删除科目成绩详情数据 {} 条", deletedSubjectScoreDetails);
        totalDeleted += deletedSubjectScoreDetails;
        
        // 4. 删除最高分记录表数据
        int deletedTopScoreRecords = topScoreRecordMapper.deleteByExamId(examId);
        log.info("删除最高分记录数据 {} 条", deletedTopScoreRecords);
        totalDeleted += deletedTopScoreRecords;
        
        // 5. 删除成绩主表数据
        int deletedExamScores = examScoreMapper.deleteByExamId(examId);
        log.info("删除成绩主表数据 {} 条", deletedExamScores);
        totalDeleted += deletedExamScores;
        
        // 6. 最后删除考试记录本身
        examTestService.deleteExamTest(examId);
        log.info("删除考试记录 1 条");
        totalDeleted += 1;
        
        log.info("已清除考试ID为 {} 的导入数据，共删除 {} 条相关记录（成绩主表 {} 条，考试记录 1 条）", 
                 examId, totalDeleted, deletedExamScores);
        
        return deletedExamScores;
    }
    
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int deleteExamScoresByImportBatchId(String importBatchId) {
        // 校验批次ID是否为空
        if (importBatchId == null || importBatchId.trim().isEmpty()) {
            throw exception(ErrorCodeConstants.EXAM_SCORE_IMPORT_BATCH_ID_NOT_EXISTS);
        }
        
        // 查询该批次下的成绩数量
        Long count = examScoreMapper.selectCountByImportBatchId(importBatchId);
        if (count == 0) {
            log.info("导入批次ID {} 下没有成绩数据，无需删除", importBatchId);
            return 0;
        }
        
        log.info("开始清除导入批次ID为 {} 的数据，共有 {} 条成绩记录", importBatchId, count);
        
        int totalDeleted = 0;
        
        // 1. 删除科目成绩详情表数据
        int deletedSubjectScoreDetails = subjectScoreDetailMapper.deleteByImportBatchId(importBatchId);
        log.info("删除科目成绩详情数据 {} 条", deletedSubjectScoreDetails);
        totalDeleted += deletedSubjectScoreDetails;
        
        // 2. 删除最高分记录表数据
        int deletedTopScoreRecords = topScoreRecordMapper.deleteByImportBatchId(importBatchId);
        log.info("删除最高分记录数据 {} 条", deletedTopScoreRecords);
        totalDeleted += deletedTopScoreRecords;
        
        // 3. 删除成绩主表数据
        int deletedExamScores = examScoreMapper.deleteByImportBatchId(importBatchId);
        log.info("删除成绩主表数据 {} 条", deletedExamScores);
        totalDeleted += deletedExamScores;
        
        log.info("已清除导入批次ID为 {} 的数据，共删除 {} 条相关记录（成绩主表 {} 条，科目详情 {} 条，最高分记录 {} 条）", 
                 importBatchId, totalDeleted, deletedExamScores, deletedSubjectScoreDetails, deletedTopScoreRecords);
        
        return deletedExamScores;
    }
    
    @Override
    public Long getScoreCountByImportBatchId(String importBatchId) {
        if (importBatchId == null || importBatchId.trim().isEmpty()) {
            return 0L;
        }
        
        // 返回成绩主表的数量（作为主要指标）
        Long scoreCount = examScoreMapper.selectCountByImportBatchId(importBatchId);
        
        // 可选：同时记录关联数据数量
        Long subjectDetailCount = subjectScoreDetailMapper.selectCountByImportBatchId(importBatchId);
        Long topScoreRecordCount = topScoreRecordMapper.selectCountByImportBatchId(importBatchId);
        log.debug("批次 {} 数据统计：成绩 {} 条，科目详情 {} 条，最高分记录 {} 条", 
                 importBatchId, scoreCount, subjectDetailCount, topScoreRecordCount);
        
        return scoreCount;
    }

    private ExamScoreDO validateExamScoreExists(Long id) {
        ExamScoreDO examScore = examScoreMapper.selectById(id);
        if (examScore == null) {
            throw exception(EXAM_SCORE_NOT_EXISTS);
        }
        return examScore;
    }

    private void validateExamScoreExists(Long examId, Long studentId, Long excludeId) {
        ExamScoreDO examScore = examScoreMapper.selectByExamIdAndStudentId(examId, studentId);
        if (examScore != null && !Objects.equals(examScore.getId(), excludeId)) {
            throw exception(EXAM_SCORE_EXISTS);
        }
    }

    @Override
    public ExamScoreDO getExamScore(Long id) {
        return examScoreMapper.selectById(id);
    }

    @Override
    public List<ExamScoreDO> getExamScoreList(Collection<Long> ids) {
        return examScoreMapper.selectList(ExamScoreDO::getId, ids);
    }

    @Override
    public PageResult<ExamScoreDO> getExamScorePage(ExamScorePageReqVO pageReqVO) {
        return examScoreMapper.selectPage(pageReqVO);
    }

    @Override
    public List<ExamScoreDO> getExamScoreList(ExamScorePageReqVO exportReqVO) {
        return examScoreMapper.selectList(exportReqVO);
    }

    @Override
    public List<ExamScoreDO> getExamScoreListByExamId(Long examId) {
        return examScoreMapper.selectListByExamId(examId);
    }

    @Override
    public List<ExamScoreDO> getExamScoreListByStudentId(Long studentId) {
        return examScoreMapper.selectListByStudentId(studentId);
    }

    @Override
    public ExamScoreDO getExamScoreByExamIdAndStudentId(Long examId, Long studentId) {
        return examScoreMapper.selectByExamIdAndStudentId(examId, studentId);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ExamScoreImportRespVO importScores(Long examId, MultipartFile file) throws IOException {
        List<ExamScoreImportExcelVO> importData = ExcelUtils.read(file, ExamScoreImportExcelVO.class);
        
        // 如果为空，则不导入
        if (importData.isEmpty()) {
            throw exception(EXAM_SCORE_IMPORT_LIST_IS_EMPTY);
        }

        // 生成本次导入的批次ID（使用时间戳+随机数保证唯一性）
        String importBatchId = "BATCH_" + System.currentTimeMillis() + "_" + UUID.randomUUID().toString().substring(0, 8);
        log.info("开始导入成绩，批次ID: {}, 数据量: {}", importBatchId, importData.size());

        ExamScoreImportRespVO respVO = ExamScoreImportRespVO.builder().createUsernames(new ArrayList<>())
                .updateUsernames(new ArrayList<>()).failureUsernames(new LinkedHashMap<>()).build();
        
        importData.forEach(importUser -> {
            try {
                // 校验，判断是否有不符合的原因
                validateExamScoreForCreate(importUser);
                
                // 设置考试ID
                importUser.setExamId(examId);
                
                // 判断如果不存在，在进行插入
                ExamScoreDO existExamScore = examScoreMapper.selectByExamIdAndStudentId(examId, importUser.getStudentId());
                if (existExamScore == null) {
                    ExamScoreDO examScore = ExamScoreConvert.INSTANCE.convert(importUser);
                    
                    // 设置导入批次ID
                    examScore.setImportBatchId(importBatchId);
                    
                    // 设置当前登录用户的部门ID（用于数据权限控制）
                    Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
                    if (currentUserDeptId != null) {
                        examScore.setDeptId(currentUserDeptId);
                        log.debug("导入成绩记录设置部门ID: {}, 学生: {}", currentUserDeptId, importUser.getStudentName());
                    } else {
                        log.warn("当前登录用户未设置部门ID，成绩记录将不设置部门信息");
                    }
                    
                    // 设置班级ID
                    ExamClassStudentDO student = examClassStudentMapper.selectById(importUser.getStudentId());
                    if (student != null && student.getClassId() != null) {
                        examScore.setClassId(student.getClassId());
                    }
                    
                    examScoreMapper.insert(examScore);
                    respVO.getCreateUsernames().add(importUser.getStudentName());
                    return;
                }
                
                // 如果存在，则进行更新
                ExamScoreDO updateExamScore = ExamScoreConvert.INSTANCE.convert(importUser);
                updateExamScore.setId(existExamScore.getId());
                
                // 更新批次ID为最新导入批次
                updateExamScore.setImportBatchId(importBatchId);
                
                // 设置当前登录用户的部门ID（用于数据权限控制）
                Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
                if (currentUserDeptId != null) {
                    updateExamScore.setDeptId(currentUserDeptId);
                    log.debug("更新成绩记录设置部门ID: {}, 学生: {}", currentUserDeptId, importUser.getStudentName());
                } else {
                    log.warn("当前登录用户未设置部门ID，成绩记录将不设置部门信息");
                }
                
                // 设置班级ID
                ExamClassStudentDO student = examClassStudentMapper.selectById(importUser.getStudentId());
                if (student != null && student.getClassId() != null) {
                    updateExamScore.setClassId(student.getClassId());
                } else {
                    log.warn("学生ID{}未找到班级信息，成绩记录将不设置部门信息", importUser.getStudentId());
                }
                
                examScoreMapper.updateById(updateExamScore);
                respVO.getUpdateUsernames().add(importUser.getStudentName());
            } catch (Exception ex) {
                respVO.getFailureUsernames().put(importUser.getStudentName(), ex.getMessage());
            }
        });
        
        // 重新计算排名
        calculateRanking(examId);
        
        return respVO;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ExamScoreImportRespVO importNewExamScores(MultipartFile file) throws IOException {
        // 1. 保存文件
        Long deptId = SecurityFrameworkUtils.getLoginUserDeptId();
        String originalFilename = file.getOriginalFilename();
        String newFilename = deptId + "_" + originalFilename;
        try {
            String path = fileApi.createFile(newFilename, null, file.getBytes());
            log.info("成绩导入文件已保存，路径：{}", path);
        } catch (Exception e) {
            log.error("保存成绩导入文件失败", e);
            // 保存文件失败不应中断主流程，记录日志即可
        }

        try {
            // 1. 优先尝试使用动态解析方式
            log.info("默认使用动态解析方式处理Excel文件");
            try {
                return importExtendedFormatExamScores(file);
            } catch (Exception dynamicParseException) {
                log.warn("动态解析失败，尝试使用旧格式解析: {}", dynamicParseException.getMessage());
                
                // 2. 动态解析失败时，回退到旧格式解析
                try {
                    return importLegacyFormatExamScores(file);
                } catch (Exception legacyParseException) {
                    log.error("旧格式解析也失败", legacyParseException);
                    throw new RuntimeException("导入失败：动态解析和旧格式解析都失败。" +
                        "动态解析错误：" + dynamicParseException.getMessage() + 
                        "；旧格式解析错误：" + legacyParseException.getMessage(), legacyParseException);
                }
            }
        } catch (Exception e) {
            log.error("成绩导入失败", e);
            throw new RuntimeException("导入失败：" + e.getMessage(), e);
        }
    }
    
    /**
     * 导入旧格式的Excel成绩文件
     */
    private ExamScoreImportRespVO importLegacyFormatExamScores(MultipartFile file) throws IOException {
        try {
            // 1. 解析Excel文件
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            
            // 检查是否有多个sheet
            int numberOfSheets = workbook.getNumberOfSheets();
            log.info("Excel文件包含{}个sheet页", numberOfSheets);
            
            ExamScoreImportRespVO finalRespVO = ExamScoreImportRespVO.builder()
                .createUsernames(new ArrayList<>())
                .updateUsernames(new ArrayList<>())
                .duplicateUsernames(new ArrayList<>())
                .failureUsernames(new LinkedHashMap<>())
                .build();
            
            // 2. 收集所有sheet的数据，而不是逐个处理
            List<NewExamScoreImportExcelVO> allImportDataList = new ArrayList<>();
            ExamInfoFromTitle examInfo = null;
            
            for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                String sheetName = sheet.getSheetName();
                log.info("开始解析第{}个sheet: {}", sheetIndex + 1, sheetName);
                
                try {
                    // 检查sheet是否为空或无效
                    if (sheet.getLastRowNum() < 3) {
                        log.warn("Sheet '{}' 数据行数不足，跳过处理", sheetName);
                        continue;
                    }
                    
                    // 解析第一行标题，提取考试信息（只在第一个有效sheet中提取）
                    if (examInfo == null) {
                        Row titleRow = sheet.getRow(0);
                        if (titleRow == null) {
                            log.warn("Sheet '{}' 缺少标题行，跳过处理", sheetName);
                            continue;
                        }
                        
                        String titleText = getCellStringValue(titleRow.getCell(0));
                        if (titleText == null || titleText.trim().isEmpty()) {
                            log.warn("Sheet '{}' 标题为空，跳过处理", sheetName);
                            continue;
                        }
                        
                        examInfo = parseExamInfoFromTitle(titleText);
                        log.info("从Sheet '{}' 解析考试信息: {}", sheetName, examInfo.getExamName());
                    }
                    
                    // 解析表头结构（第2-3行）
                    Row headerRow1 = sheet.getRow(1); // 主表头
                    Row headerRow2 = sheet.getRow(2); // 子表头
                    
                    if (headerRow1 == null || headerRow2 == null) {
                        log.warn("Sheet '{}' 缺少表头行，跳过处理", sheetName);
                        continue;
                    }
                    
                    // 使用动态表头解析器创建字段映射（避免固定列索引导致的数据错位）
                    DynamicHeaderParser.HeaderMapping headerMapping = dynamicHeaderParser.parseHeaders(headerRow1, headerRow2);
                    
                    // 验证表头映射
                    if (!dynamicHeaderParser.validateRequiredFields(headerMapping)) {
                        log.error("Sheet '{}' 缺少必需的表头字段", sheetName);
                        finalRespVO.getFailureUsernames().put("Sheet_" + sheetName, "表头缺少必需字段");
                        continue;
                    }
                    
                    // 解析数据行并收集到总列表中
                    List<NewExamScoreImportExcelVO> sheetImportDataList = new ArrayList<>();
                    for (int i = 3; i <= sheet.getLastRowNum(); i++) {
                        Row dataRow = sheet.getRow(i);
                        if (dataRow == null) continue;
                        
                        // 使用动态解析器解析数据行，支持灵活的列位置
                        NewExamScoreImportExcelVO importData = parseDataRowDynamic(dataRow, headerMapping);
                        if (importData != null && importData.getStudentName() != null) {
                            sheetImportDataList.add(importData);
                        }
                    }
                    
                    if (sheetImportDataList.isEmpty()) {
                        log.warn("Sheet '{}' 没有有效的学生数据，跳过处理", sheetName);
                        continue;
                    }
                    
                    log.info("Sheet '{}' 解析到{}条学生数据", sheetName, sheetImportDataList.size());
                    allImportDataList.addAll(sheetImportDataList);
                    
                } catch (Exception e) {
                    log.error("解析Sheet '{}'时发生错误: {}", sheetName, e.getMessage(), e);
                    finalRespVO.getFailureUsernames().put("Sheet_" + sheetName, "解析失败: " + e.getMessage());
                }
            }
            
            // 3. 如果没有解析到任何有效数据，直接返回
            if (allImportDataList.isEmpty() || examInfo == null) {
                log.warn("Excel文件中没有有效的学生数据或考试信息");
                workbook.close();
                return finalRespVO;
            }
            
            log.info("所有sheet解析完成，总计{}条学生数据", allImportDataList.size());
            
            // 4. 统一处理所有数据：批量自动创建班级和学生
            AutoGenerationService.BatchCreationResult creationResult = 
                autoGenerationService.batchAutoCreate(allImportDataList);
            
            // 5. 从所有数据中提取班级ID列表，创建一个考试记录
            List<Long> allClassIds = extractClassIdsFromImportData(allImportDataList);
            Long examId = createExamFromTitleWithClasses(examInfo, allClassIds);
            
            log.info("创建考试成功，考试ID: {}，包含{}个班级", examId, allClassIds.size());
            
            // 6. 处理所有成绩数据并保存到同一个考试中
            ExamScoreImportRespVO respVO = processNewExamScores(examId, allImportDataList, creationResult);
            
            // 7. 更新班级学生计数
            updateClassStudentCounts(allClassIds);
            
            // 8. 异步计算排名（不阻塞导入流程）
            if (needsRankingCalculation(examId)) {
                log.info("成绩导入完成，开始异步计算排名...");
                asyncCalculateRankings(examId);
            } else {
                log.info("成绩导入完成，跳过排名计算");
            }
            
            workbook.close();
            
            log.info("多Sheet导入完成，创建了1个考试(ID: {})，总计创建{}个用户，更新{}个用户，失败{}个", 
                    examId,
                    respVO.getCreateUsernames().size(), 
                    respVO.getUpdateUsernames().size(), 
                    respVO.getFailureUsernames().size());
            
            return respVO;
            
        } catch (Exception e) {
            log.error("新高考成绩导入失败", e);
            throw new RuntimeException("导入失败：" + e.getMessage(), e);
        }
    }
    
    /**
     * 导入新格式的Excel成绩文件（使用动态解析器）
     */
    private ExamScoreImportRespVO importExtendedFormatExamScores(MultipartFile file) throws IOException {
        try {
            log.info("开始导入新格式Excel成绩文件（使用动态解析器）");
            
            // 1. 使用POI读取Excel文件进行动态解析
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            
            // 1.1 提取标题行文本（用于考试名称）
            String titleText = extractTitleTextFromSheet(sheet);
            log.info("提取到标题文本: {}", titleText);
            
            // 2. 自动检测表头行数并解析表头，获取字段映射
            DynamicHeaderParser.HeaderMapping headerMapping = parseHeadersWithAutoDetection(sheet);
            
            // 2.1 验证表头映射
            DynamicHeaderParser.ValidationResult headerValidation = dynamicHeaderParser.validateHeaderMapping(headerMapping);
            if (headerValidation.hasErrors()) {
                log.error("表头验证失败: {}", headerValidation.getSummary());
                throw new RuntimeException("表头验证失败: " + String.join("; ", headerValidation.getErrors()));
            }
            if (headerValidation.hasWarnings()) {
                log.warn("表头验证警告: {}", headerValidation.getSummary());
            }
            
            log.info("动态解析表头完成，检测到表格类型：{}，非计分科目：{}", 
                    headerMapping.getTableType(), headerMapping.getNonScoringSubjects());
            
            // 3. 解析数据行（从标题行+表头行之后开始）
            int titleRowIndex = findTitleRowIndex(sheet);
            int headerStartRow = titleRowIndex >= 0 ? titleRowIndex + 1 : 0;
            int headerRowCount = detectHeaderRowCountFromIndex(sheet, headerStartRow);
            int dataStartRow = headerStartRow + headerRowCount;
            
            log.info("数据行起始位置：标题行={}, 表头起始行={}, 表头行数={}, 数据起始行={}", 
                    titleRowIndex, headerStartRow, headerRowCount, dataStartRow);
            
            List<NewExamScoreImportExcelVO> importDataList = new ArrayList<>();
            List<String> validationWarnings = new ArrayList<>();
            List<String> validationErrors = new ArrayList<>();
            
            for (int i = dataStartRow; i <= sheet.getLastRowNum(); i++) {
                Row dataRow = sheet.getRow(i);
                if (dataRow != null) {
                    NewExamScoreImportExcelVO importData = parseDataRowDynamic(dataRow, headerMapping);
                    if (importData != null && importData.isDataComplete()) {
                        // 3.1 验证解析后的数据
                        DynamicDataRowParser.DataValidationResult dataValidation = 
                            dynamicDataRowParser.validateParsedData(importData, headerMapping);
                        
                        if (dataValidation.hasErrors()) {
                            validationErrors.add("第" + (i + 1) + "行: " + String.join("; ", dataValidation.getErrors()));
                            log.warn("第{}行数据验证失败: {}", i + 1, dataValidation.getSummary());
                            continue; // 跳过有错误的数据行
                        }
                        
                        if (dataValidation.hasWarnings()) {
                            validationWarnings.add("第" + (i + 1) + "行: " + String.join("; ", dataValidation.getWarnings()));
                        }
                        
                        importDataList.add(importData);
                    }
                }
            }
            
            workbook.close();
            
            // 3.2 处理验证结果
            if (!validationErrors.isEmpty()) {
                log.error("数据验证发现{}个错误: {}", validationErrors.size(), validationErrors);
                throw new RuntimeException("数据验证失败，发现" + validationErrors.size() + "个错误。详情: " + 
                    String.join("; ", validationErrors.subList(0, Math.min(3, validationErrors.size()))) + 
                    (validationErrors.size() > 3 ? "..." : ""));
            }
            
            if (!validationWarnings.isEmpty()) {
                log.warn("数据验证发现{}个警告: {}", validationWarnings.size(), validationWarnings);
            }
            
            if (importDataList.isEmpty()) {
                throw new RuntimeException("Excel文件中没有有效的学生数据");
            }
            
            log.info("动态解析数据完成，共{}条有效记录，{}个警告", importDataList.size(), validationWarnings.size());
            
            // 4. 批量自动创建班级和学生
            AutoGenerationService.BatchCreationResult creationResult = 
                autoGenerationService.batchAutoCreate(importDataList);
            
            // 5. 创建考试记录
            ExamInfoFromTitle examInfo = createDefaultExamInfoFromDynamic(importDataList, headerMapping, titleText);
            List<Long> classIds = extractClassIdsFromImportData(importDataList);
            Long examId = createExamFromTitleWithClasses(examInfo, classIds);
            
            // 6. 处理成绩数据并保存
            ExamScoreImportRespVO respVO = processNewExamScores(examId, importDataList, creationResult);
            
            // 7. 更新班级学生计数
            updateClassStudentCounts(classIds);
            
            // 8. 异步计算排名（不阻塞导入流程）
            if (needsRankingCalculation(examId)) {
                log.info("成绩导入完成，开始异步计算排名...");
                asyncCalculateRankings(examId);
            } else {
                log.info("成绩导入完成，跳过排名计算");
            }
            
            log.info("新格式Excel导入完成，创建{}个用户，更新{}个用户，失败{}个", 
                    respVO.getCreateUsernames().size(), 
                    respVO.getUpdateUsernames().size(), 
                    respVO.getFailureUsernames().size());
            
            return respVO;
            
        } catch (Exception e) {
            log.error("新格式Excel成绩导入失败", e);
            throw new RuntimeException("导入失败：" + e.getMessage(), e);
        }
    }
    
    /**
     * 从新格式数据中提取班级ID列表
     */
    private List<Long> extractClassIdsFromExtendedData(List<ExtendedExamScoreImportExcelVO> extendedDataList) {
        Set<String> classNames = extendedDataList.stream()
            .map(ExtendedExamScoreImportExcelVO::getClassName)
            .filter(className -> className != null && !className.trim().isEmpty())
            .collect(Collectors.toSet());
        
        List<Long> classIds = new ArrayList<>();
        for (String className : classNames) {
            ExamClassDO examClass = examClassMapper.selectByClassName(className);
            if (examClass != null) {
                classIds.add(examClass.getId());
            }
        }
        
        return classIds;
    }
    
    /**
     * 为新格式数据创建默认考试信息
     */
    private ExamInfoFromTitle createDefaultExamInfo(List<ExtendedExamScoreImportExcelVO> extendedDataList) {
        ExamInfoFromTitle examInfo = new ExamInfoFromTitle();
        
        // 从数据中分析考试信息
        String subjectCombination = extendedDataList.stream()
            .map(ExtendedExamScoreImportExcelVO::getSubjectCombination)
            .filter(Objects::nonNull)
            .findFirst()
            .orElse("物理方向");
        
        // 设置默认考试信息
        examInfo.setExamName("导入考试-" + LocalDateTime.now().toString().substring(0, 19));
        examInfo.setExamType("综合测试");
        examInfo.setGradeLevel("高三");
        examInfo.setSemester("第一学期");
        examInfo.setSubjectDirection(subjectCombination.contains("历史") ? "历史方向" : "物理方向");
        examInfo.setExamCategory("期中测试");
        
        return examInfo;
    }
    
    /**
     * 从动态解析的数据创建默认考试信息
     */
    private ExamInfoFromTitle createDefaultExamInfoFromDynamic(List<NewExamScoreImportExcelVO> importDataList, 
                                                               DynamicHeaderParser.HeaderMapping headerMapping,
                                                               String titleText) {
        ExamInfoFromTitle examInfo = new ExamInfoFromTitle();
        
        // 使用表格第一行的标题文本作为考试名称，如果没有标题则使用时间戳
        if (titleText != null && !titleText.trim().isEmpty()) {
            examInfo.setExamName(titleText.trim());
            log.info("使用标题行文本作为考试名称: {}", titleText);
        } else {
            examInfo.setExamName("导入考试_" + LocalDateTime.now().format(java.time.format.DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss")));
            log.warn("未找到标题行文本，使用默认时间戳作为考试名称");
        }
        
        // 使用更合理的默认值，避免误导
        examInfo.setExamType("");  // 考试类型留空，避免默认值误导
        examInfo.setGradeLevel("");  // 年级留空，由管理员根据实际情况填写
        examInfo.setSemester("");  // 学期留空，由管理员根据实际情况填写
        
        // 根据表格类型设置科目方向
        if ("PHYSICS".equals(headerMapping.getTableType())) {
            examInfo.setSubjectDirection("物理方向");
        } else if ("HISTORY".equals(headerMapping.getTableType())) {
            examInfo.setSubjectDirection("历史方向");
        } else {
            examInfo.setSubjectDirection("综合");
        }
        
        examInfo.setExamCategory("统考");
        return examInfo;
    }
    
    /**
     * 从标题文本解析考试信息
     */
    private ExamInfoFromTitle parseExamInfoFromTitle(String titleText) {
        ExamInfoFromTitle examInfo = new ExamInfoFromTitle();
        
        // 解析标题：综合成绩表（组合排名）-总分-宣城市第十三中学-物理方向-江淮十校25年8月高三第一次联考-赋分报告
        // 只保留：物理方向-江淮十校25年8月高三第一次联考
        String simplifiedExamName = titleText.trim();
        
        if (titleText.contains("-")) {
            String[] parts = titleText.split("-");
            if (parts.length >= 2) {
                examInfo.setExamType(parts[1].trim()); // 总分
            }
            if (parts.length >= 3) {
                examInfo.setSubjectDirection(parts[2].trim()); // 物理方向
            }
            if (parts.length >= 4) {
                String gradeAndSemester = parts[3].trim();
                if (gradeAndSemester.contains("（") && gradeAndSemester.contains("）")) {
                    int start = gradeAndSemester.indexOf("（") + 1;
                    int end = gradeAndSemester.indexOf("）");
                    examInfo.setGradeLevel(gradeAndSemester.substring(start, end));
                    examInfo.setSemester(gradeAndSemester.substring(end + 1));
                }
            }
            if (parts.length >= 5) {
                examInfo.setExamCategory(parts[4].trim()); // 期中素质测试
            }
            
            // 生成简化的考试名称：只保留物理方向和联考名称部分
            // 从标题中提取：物理方向-江淮十校25年8月高三第一次联考
            if (parts.length >= 5) {
                // 找到物理方向的位置（通常是第4个部分，索引为3）
                // 找到联考名称的位置（通常是第5个部分，索引为4）
                String subjectDirection = null;
                String examName = null;
                
                // 查找包含"方向"的部分
                for (int i = 0; i < parts.length; i++) {
                    if (parts[i].contains("方向")) {
                        subjectDirection = parts[i].trim();
                        // 下一个部分通常是联考名称
                        if (i + 1 < parts.length) {
                            examName = parts[i + 1].trim();
                        }
                        break;
                    }
                }
                
                // 如果找到了方向和联考名称，组合成简化名称
                if (subjectDirection != null && examName != null) {
                    simplifiedExamName = subjectDirection + "-" + examName;
                }
            }
        }
        
        examInfo.setExamName(simplifiedExamName);
        return examInfo;
    }
    
    /**
     * 从导入数据中提取班级ID列表
     */
    private List<Long> extractClassIdsFromImportData(List<NewExamScoreImportExcelVO> importDataList) {
        Set<String> classNames = importDataList.stream()
            .map(NewExamScoreImportExcelVO::getClassName)
            .filter(className -> className != null && !className.trim().isEmpty())
            .collect(Collectors.toSet());
        
        log.info("从导入数据中提取到{}个不同的班级名称：{}", classNames.size(), classNames);
        
        List<Long> classIds = new ArrayList<>();
        for (String className : classNames) {
            // 通过班级名称查找班级ID
            ExamClassDO examClass = examClassMapper.selectByClassName(className);
            if (examClass != null) {
                classIds.add(examClass.getId());
                log.debug("找到班级 [{}] ID={}", className, examClass.getId());
            } else {
                log.warn("未找到班级 [{}]", className);
            }
        }
        
        log.info("成功提取{}个班级ID：{}", classIds.size(), classIds);
        return classIds;
    }
    
    /**
     * 根据解析的考试信息和班级ID列表创建考试记录
     */
    private Long createExamFromTitleWithClasses(ExamInfoFromTitle examInfo, List<Long> classIds) {
        // 创建考试记录的VO
        ExamTestCreateReqVO createReqVO = new ExamTestCreateReqVO();
        createReqVO.setExamName(examInfo.getExamName());
        createReqVO.setExamType(examInfo.getExamType());
        createReqVO.setGradeLevel(examInfo.getGradeLevel());
        createReqVO.setSemester(examInfo.getSemester());
        createReqVO.setSubjectDirection(examInfo.getSubjectDirection());
        createReqVO.setExamCategory(examInfo.getExamCategory());
        createReqVO.setStatus(1); // 启用状态
        
        // 设置班级ID列表
        createReqVO.setClassIds(classIds != null ? classIds : new ArrayList<>());
        
        // 设置默认的考试时间为当前时间
        createReqVO.setExamDate(LocalDateTime.now());
        
        return examTestService.createExamTest(createReqVO);
    }
    
    /**
     * 检测表格类型（物理方向或历史方向）
     */
    private String detectTableType(Row headerRow1, Row headerRow2) {
        // 检查表头中是否包含"物理"或"历史"关键字
        for (int i = 0; i < headerRow1.getLastCellNum(); i++) {
            String cellValue = getCellStringValue(headerRow1.getCell(i));
            if (cellValue != null) {
                if (cellValue.contains("物理")) {
                    return "PHYSICS";
                } else if (cellValue.contains("历史")) {
                    return "HISTORY";
                }
            }
        }
        
        // 如果第一行没找到，检查第二行
        for (int i = 0; i < headerRow2.getLastCellNum(); i++) {
            String cellValue = getCellStringValue(headerRow2.getCell(i));
            if (cellValue != null) {
                if (cellValue.contains("物理")) {
                    return "PHYSICS";
                } else if (cellValue.contains("历史")) {
                    return "HISTORY";
                }
            }
        }
        
        // 默认返回物理方向
        log.warn("无法检测表格类型，默认使用物理方向");
        return "PHYSICS";
    }
    
    /**
     * 自动检测表头行数并使用多层表头解析
     */
    private DynamicHeaderParser.HeaderMapping parseHeadersWithAutoDetection(Sheet sheet) {
        // 首先检测并跳过标题行
        int titleRowIndex = findTitleRowIndex(sheet);
        int headerStartRow = titleRowIndex >= 0 ? titleRowIndex + 1 : 0;
        
        log.info("标题行索引: {}, 表头起始行: {}", titleRowIndex, headerStartRow);
        
        // 检测表头行数（从标题行后开始）
        int headerRowCount = detectHeaderRowCountFromIndex(sheet, headerStartRow);
        log.info("检测到表头行数: {}", headerRowCount);
        
        // 收集表头行（从标题行后开始）
        List<Row> headerRows = new ArrayList<>();
        for (int i = 0; i < headerRowCount; i++) {
            Row row = sheet.getRow(headerStartRow + i);
            if (row != null) {
                headerRows.add(row);
            }
        }
        
        // 使用多层表头解析
        return dynamicHeaderParser.parseMultiLevelHeaders(headerRows);
    }
    
    /**
     * 从Sheet中提取标题行文本
     * @param sheet Excel工作表
     * @return 标题行文本，如果没有找到返回null
     */
    private String extractTitleTextFromSheet(Sheet sheet) {
        int titleRowIndex = findTitleRowIndex(sheet);
        if (titleRowIndex >= 0) {
            Row titleRow = sheet.getRow(titleRowIndex);
            if (titleRow != null) {
                // 获取第一个非空单元格的内容作为标题
                for (Cell cell : titleRow) {
                    String cellValue = getCellStringValue(cell);
                    if (cellValue != null && !cellValue.trim().isEmpty()) {
                        log.info("从第{}行提取到标题: {}", titleRowIndex + 1, cellValue.trim());
                        return cellValue.trim();
                    }
                }
            }
        }
        
        // 如果没有找到符合条件的标题行，尝试读取第一行的第一个非空单元格
        log.warn("未找到符合标题行特征的行，尝试读取第一行内容作为标题");
        if (sheet.getLastRowNum() >= 0) {
            Row firstRow = sheet.getRow(0);
            if (firstRow != null) {
                for (Cell cell : firstRow) {
                    String cellValue = getCellStringValue(cell);
                    if (cellValue != null && !cellValue.trim().isEmpty()) {
                        // 检查是否是表头行（如果是表头行，说明第一行不是标题）
                        if (isHeaderRow(firstRow)) {
                            log.info("第一行是表头行，不提取标题");
                            return null;
                        }
                        log.info("从第1行提取到标题（降级处理）: {}", cellValue.trim());
                        return cellValue.trim();
                    }
                }
            }
        }
        
        return null;
    }
    
    /**
     * 查找标题行的索引
     * @return 标题行索引，如果没有找到返回-1
     */
    private int findTitleRowIndex(Sheet sheet) {
        int maxRowsToCheck = Math.min(3, sheet.getLastRowNum() + 1);
        
        for (int i = 0; i < maxRowsToCheck; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                // 统计非空单元格数量和第一个单元格内容
                int nonEmptyCells = 0;
                String firstCellValue = null;
                for (Cell cell : row) {
                    if (cell != null) {
                        String cellValue = getCellStringValue(cell);
                        if (cellValue != null && !cellValue.trim().isEmpty()) {
                            nonEmptyCells++;
                            if (firstCellValue == null) {
                                firstCellValue = cellValue;
                            }
                        }
                    }
                }
                
                boolean isTitle = isTitleRow(row);
                log.info("第{}行: 非空单元格数={}, 第一个单元格='{}', 是否为标题={}", 
                        i, nonEmptyCells, 
                        firstCellValue != null && firstCellValue.length() > 50 
                            ? firstCellValue.substring(0, 50) + "..." 
                            : firstCellValue, 
                        isTitle);
                
                if (isTitle) {
                    log.info("识别到标题行: 第{}行", i);
                    return i;
                }
            }
        }
        
        log.warn("未找到标题行，将假设从第0行开始是表头");
        return -1;
    }
    
    /**
     * 从指定行开始检测表头行数
     */
    private int detectHeaderRowCountFromIndex(Sheet sheet, int startRowIndex) {
        int maxRowsToCheck = Math.min(startRowIndex + 5, sheet.getLastRowNum() + 1);
        int headerRowCount = 0;
        
        for (int rowIndex = startRowIndex; rowIndex < maxRowsToCheck; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                if (headerRowCount > 0) {
                    break;
                }
                continue;
            }
            
            boolean isHeader = isHeaderRow(row);
            boolean isData = isDataRow(row);
            
            log.debug("第{}行 - 表头特征: {}, 数据特征: {}", rowIndex + 1, isHeader, isData);
            
            if (isHeader) {
                headerRowCount++;
                continue;
            }
            if (isData) {
                break;
            }
        }
        
        // 至少2行表头支持多级表头
        return Math.max(2, headerRowCount);
    }
    
    /**
     * 检测表头行数
     * 通过分析前几行的内容特征来判断哪些行是表头
     */
    private int detectHeaderRowCount(Sheet sheet) {
        int maxRowsToCheck = Math.min(6, sheet.getLastRowNum() + 1);
        int headerRowCount = 0; // 统计实际的表头行数
        int titleRowsSkipped = 0; // 跳过的标题行数

        for (int rowIndex = 0; rowIndex < maxRowsToCheck; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                // 空行不计入；若已出现表头行后遇到空行，认为表头结束
                if (headerRowCount > 0) {
                    break;
                }
                titleRowsSkipped++;
                continue;
            }

            boolean isHeader = isHeaderRow(row);
            boolean isData = isDataRow(row);
            boolean isTitle = isTitleRow(row);

            log.debug("第{}行 - 标题特征: {}, 表头特征: {}, 数据特征: {}", rowIndex + 1, isTitle, isHeader, isData);

            if (isHeader) {
                // 连续的表头行（多层表头）
                headerRowCount++;
                continue;
            }
            if (isData) {
                // 遇到首个数据行，表头结束
                break;
            }
            // 既不是表头也不是数据（例如标题或说明行）
            if (headerRowCount == 0 && isTitle) {
                // 还没有遇到表头，且当前行是标题行，跳过
                titleRowsSkipped++;
                continue;
            }
            // 如果已经开始计数表头，但这行既不是表头也不是数据，则表头可能结束了
            if (headerRowCount > 0) {
                break;
            }
        }

        // 如果没有检测到表头，可能是因为跳过了标题行，尝试从下一行开始
        // 通常表格格式是：第0行标题，第1-2行表头，第3行开始数据
        // 至少返回2行表头（支持多级表头），避免后续解析失败
        if (headerRowCount == 0 && titleRowsSkipped > 0) {
            log.warn("未检测到表头行，但跳过了{}行标题，默认使用第{}-{}行作为表头", titleRowsSkipped, titleRowsSkipped + 1, titleRowsSkipped + 2);
            return 2; // 默认使用2行表头
        }
        
        return Math.max(2, headerRowCount); // 至少2行表头支持多级表头
    }
    
    /**
     * 判断是否为标题行
     * 标题行的特征：非空单元格数量很少（通常1-3个，因为是合并单元格），且第一个单元格内容较长
     */
    private boolean isTitleRow(Row row) {
        if (row == null) return false;
        
        int nonEmptyCells = 0;
        int totalCells = row.getLastCellNum();
        String firstCellValue = null;
        
        for (Cell cell : row) {
            if (cell != null) {
                String cellValue = getCellStringValue(cell);
                if (cellValue != null && !cellValue.trim().isEmpty()) {
                    nonEmptyCells++;
                    if (firstCellValue == null) {
                        firstCellValue = cellValue;
                    }
                }
            }
        }
        
        // 标题行的结构特征：
        // 1. 非空单元格数量很少（<= 3个），因为通常是合并单元格
        // 2. 总列数要大于5（确保是成绩表，不是简单的文本文档）
        // 3. 第一个单元格内容较长（通常包含完整的标题信息）
        if (nonEmptyCells <= 3 && totalCells > 5) {
            if (firstCellValue != null && firstCellValue.length() > 10) {
                return true;
            }
        }
        
        return false;
    }
    
    /**
     * 判断是否为表头行
     * 表头行通常包含字段名称，如"姓名"、"学号"、"语文"等
     */
    private boolean isHeaderRow(Row row) {
        if (row == null) return false;
        
        int headerFieldCount = 0;
        int totalCells = 0;
        
        // 定义表头关键字
        String[] headerKeywords = {
            "姓名", "学号", "班级", "学生", "总分", "选科", "排名",
            "语文", "数学", "英语", "物理", "化学", "生物", "历史", "地理", "政治",
            "俄语", "日语", "西班牙语", "德语", "法语",
            "原始", "赋分", "等级", "联考", "校内", "班内",
            "最高分", "第一", "第二"
        };
        
        for (Cell cell : row) {
            if (cell == null) continue;
            totalCells++;
            
            String cellValue = getCellStringValue(cell);
            if (cellValue != null && !cellValue.trim().isEmpty()) {
                // 检查是否包含表头关键字
                for (String keyword : headerKeywords) {
                    if (cellValue.contains(keyword)) {
                        headerFieldCount++;
                        break;
                    }
                }
            }
        }
        
        // 如果超过30%的单元格包含表头关键字，认为是表头行
        return totalCells > 0 && (double) headerFieldCount / totalCells >= 0.3;
    }
    
    /**
     * 判断是否为数据行
     * 数据行通常包含学生姓名、学号和成绩等具体数据
     */
    private boolean isDataRow(Row row) {
        if (row == null) return false;
        
        boolean hasStudentName = false;
        boolean hasStudentId = false;
        boolean hasScores = false;
        int numericCellCount = 0;
        int totalCells = 0;
        
        for (Cell cell : row) {
            if (cell == null) continue;
            totalCells++;
            
            String cellValue = getCellStringValue(cell);
            if (cellValue != null && !cellValue.trim().isEmpty()) {
                // 检查是否为学生姓名（通常是中文姓名，2-4个字符）
                if (cellValue.matches("^[\\u4e00-\\u9fa5]{2,4}$")) {
                    hasStudentName = true;
                }
                
                // 检查是否为学号（通常是数字）
                if (cellValue.matches("^\\d+$")) {
                    hasStudentId = true;
                }
            }
            
            // 检查是否为数值（成绩）
            if (cell.getCellType() == CellType.NUMERIC) {
                numericCellCount++;
                hasScores = true;
            }
        }
        
        // 数据行应该有学生姓名，并且有一定比例的数值数据
        return hasStudentName && hasScores && totalCells > 0 && 
               (double) numericCellCount / totalCells >= 0.2;
    }
    
    /**
     * 使用动态解析器解析数据行 - 支持灵活的列位置检测
     */
    private NewExamScoreImportExcelVO parseDataRowDynamic(Row dataRow, DynamicHeaderParser.HeaderMapping headerMapping) {
        return dynamicDataRowParser.parseDataRow(dataRow, headerMapping);
    }
    
    /**
     * 解析数据行 - 支持物理方向和历史方向两种表格格式（旧版本，保持兼容性）
     */
    private NewExamScoreImportExcelVO parseDataRow(Row dataRow, Row headerRow1, Row headerRow2) {
        NewExamScoreImportExcelVO importData = new NewExamScoreImportExcelVO();
        
        // 检测表格类型
        String tableType = detectTableType(headerRow1, headerRow2);
        log.debug("检测到表格类型: {}", tableType);
        
        // 基础信息
        importData.setExamNo(getCellStringValue(dataRow.getCell(0)));
        importData.setStudentName(getCellStringValue(dataRow.getCell(1)));
        importData.setClassName(getCellStringValue(dataRow.getCell(2)));
        importData.setSubjectCombination(getCellStringValue(dataRow.getCell(3)));
        
        // 总分信息（6列：原始分、赋分、联考排名、组合排名、校内排名、班级排名）
        importData.setTotalOriginalScore(getCellBigDecimalValue(dataRow.getCell(4)));
        importData.setTotalAssignedScore(getCellBigDecimalValue(dataRow.getCell(5)));
        // 读取联考排名（第6列）
        importData.setTotalUnionRank(getCellIntegerValue(dataRow.getCell(6)));
        importData.setCombinationRank(getCellIntegerValue(dataRow.getCell(7)));
        importData.setSchoolRank(getCellIntegerValue(dataRow.getCell(8)));
        importData.setClassRank(getCellIntegerValue(dataRow.getCell(9)));
        
        // 各科目成绩（从第10列开始）
        int colIndex = 10;
        
        // 语文（4列：原始分、联考排名、校内排名、班级排名）
        importData.setChineseOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setChineseUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setChineseSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setChineseClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 数学（4列：原始分、联考排名、校内排名、班级排名）
        importData.setMathOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setMathUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setMathSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setMathClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 英语（4列：原始分、联考排名、校内排名、班级排名）
        importData.setEnglishOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setEnglishUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setEnglishSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setEnglishClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 根据表格类型解析物理或历史科目
        if ("PHYSICS".equals(tableType)) {
            // 物理方向：解析物理科目（4列：原始分、联考排名、校内排名、班级排名）
            importData.setPhysicsOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
            importData.setPhysicsUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
            importData.setPhysicsSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
            importData.setPhysicsClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        } else if ("HISTORY".equals(tableType)) {
            // 历史方向：解析历史科目（4列：原始分、联考排名、校内排名、班级排名）
            importData.setHistoryOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
            importData.setHistoryUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
            importData.setHistorySchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
            importData.setHistoryClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        }
        
        // 化学（6列：原始分、赋分、赋分等级、联考排名、校内排名、班级排名）
        importData.setChemistryOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setChemistryAssignedScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setChemistryGradeLevel(getCellStringValue(dataRow.getCell(colIndex++)));
        importData.setChemistryUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setChemistrySchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setChemistryClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 生物（6列：原始分、赋分、赋分等级、联考排名、校内排名、班级排名）
        importData.setBiologyOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setBiologyAssignedScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setBiologyGradeLevel(getCellStringValue(dataRow.getCell(colIndex++)));
        importData.setBiologyUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setBiologySchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setBiologyClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 政治（6列：原始分、赋分、赋分等级、联考排名、校内排名、班级排名）
        importData.setPoliticsOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setPoliticsAssignedScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setPoliticsGradeLevel(getCellStringValue(dataRow.getCell(colIndex++)));
        importData.setPoliticsUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setPoliticsSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setPoliticsClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 地理（6列：原始分、赋分、赋分等级、联考排名、校内排名、班级排名）
        importData.setGeographyOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setGeographyAssignedScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setGeographyGradeLevel(getCellStringValue(dataRow.getCell(colIndex++)));
        importData.setGeographyUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setGeographySchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setGeographyClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 俄语（4列：原始分、联考排名、校内排名、班级排名）- 注意：小语种没有赋分和赋分等级
        importData.setRussianOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setRussianUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setRussianSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setRussianClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 日语（4列：原始分、联考排名、校内排名、班级排名）- 注意：小语种没有赋分和赋分等级
        importData.setJapaneseOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setJapaneseUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setJapaneseSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setJapaneseClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 西班牙语（4列：原始分、联考排名、校内排名、班级排名）- 注意：小语种没有赋分和赋分等级
        importData.setSpanishOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setSpanishUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setSpanishSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setSpanishClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 跳过历史A(不计分)（4列：原始分、联考排名、校内排名、班级排名）
        colIndex += 4;
        
        // 跳过历史B(不计分)（4列：原始分、联考排名、校内排名、班级排名）
        colIndex += 4;
        
        // 跳过生物A(不计分)（4列：原始分、联考排名、校内排名、班级排名）
        colIndex += 4;
        
        // 跳过生物B(不计分)（4列：原始分、联考排名、校内排名、班级排名）
        colIndex += 4;
        
        // 第一高分（6列：科目、原始分、赋分、联考排名、校内排名、班级排名）
        importData.setFirstTopSubject(getCellStringValue(dataRow.getCell(colIndex++)));
        importData.setFirstTopOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setFirstTopAssignedScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setFirstTopUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setFirstTopSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setFirstTopClassRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        
        // 第二高分（6列：科目、原始分、赋分、联考排名、校内排名、班级排名）
        importData.setSecondTopSubject(getCellStringValue(dataRow.getCell(colIndex++)));
        importData.setSecondTopOriginalScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setSecondTopAssignedScore(getCellBigDecimalValue(dataRow.getCell(colIndex++)));
        importData.setSecondTopUnionRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setSecondTopSchoolRank(getCellIntegerValue(dataRow.getCell(colIndex++)));
        importData.setSecondTopClassRank(getCellIntegerValue(dataRow.getCell(colIndex)));
        
        return importData;
    }
    
    /**
     * 处理新高考成绩数据（优化版本：使用批量操作）
     */
    private ExamScoreImportRespVO processNewExamScores(Long examId, 
                                                       List<NewExamScoreImportExcelVO> importDataList,
                                                       AutoGenerationService.BatchCreationResult creationResult) {
        
        log.info("开始批量处理{}条成绩数据", importDataList.size());
        long startTime = System.currentTimeMillis();
        
        // 生成本次导入的批次ID（使用时间戳+随机数保证唯一性）
        String importBatchId = "BATCH_" + System.currentTimeMillis() + "_" + UUID.randomUUID().toString().substring(0, 8);
        log.info("生成导入批次ID: {}", importBatchId);
        
        ExamScoreImportRespVO respVO = ExamScoreImportRespVO.builder()
            .createUsernames(new ArrayList<>())
            .updateUsernames(new ArrayList<>())
            .duplicateUsernames(new ArrayList<>())
            .failureUsernames(new LinkedHashMap<>())
            .build();
        
        // === 第一步：批量预处理和数据准备 ===
        
        // 1.1 批量创建唯一的选科组合（去重）
        Set<String> uniqueCombinations = importDataList.stream()
            .map(NewExamScoreImportExcelVO::getSubjectCombination)
            .filter(Objects::nonNull)
            .collect(Collectors.toSet());
        log.info("发现{}个唯一选科组合，开始批量创建", uniqueCombinations.size());
        for (String combination : uniqueCombinations) {
            try {
                subjectCombinationService.autoCreateSubjectCombination(combination);
            } catch (Exception e) {
                log.warn("创建选科组合{}失败: {}", combination, e.getMessage());
            }
        }
        
        // 1.2 为所有导入数据设置学生ID
        for (NewExamScoreImportExcelVO importData : importDataList) {
            try {
                Long studentId = getStudentIdFromCreationResult(creationResult, importData);
                importData.setStudentId(studentId);
            } catch (Exception e) {
                log.error("获取学生{}的ID失败: {}", importData.getStudentName(), e.getMessage());
                respVO.getFailureUsernames().put(importData.getStudentName(), "无法获取学生ID: " + e.getMessage());
            }
        }
        
        // 过滤掉没有学生ID的数据
        List<NewExamScoreImportExcelVO> validDataList = importDataList.stream()
            .filter(data -> data.getStudentId() != null)
            .collect(Collectors.toList());
        log.info("有效数据{}条（过滤掉{}条无效数据）", validDataList.size(), importDataList.size() - validDataList.size());
        
        // 【修复】Excel内部数据去重：检测并移除重复的学生记录
        // 如果同一个学生在Excel中出现多次，保留最后一条记录
//        Map<Long, NewExamScoreImportExcelVO> deduplicatedMap = new LinkedHashMap<>();
//        int duplicateCount = 0;
//        for (NewExamScoreImportExcelVO data : validDataList) {
//            Long studentId = data.getStudentId();
//            if (deduplicatedMap.containsKey(studentId)) {
//                duplicateCount++;
//                log.warn("Excel中发现重复学生记录：学生姓名={}, 班级={}, 学生ID={}，将保留最后一条记录",
//                    data.getStudentName(), data.getClassName(), studentId);
//                respVO.getDuplicateUsernames().add(data.getStudentName() + "(重复" + duplicateCount + "次)");
//            }
//            deduplicatedMap.put(studentId, data); // 后面的覆盖前面的，保留最后一条
//        }
        
        // 使用去重后的数据
//        validDataList = new ArrayList<>(deduplicatedMap.values());
//        if (duplicateCount > 0) {
//            log.warn("Excel中共发现{}条重复学生记录，已自动去重", duplicateCount);
//        }
//        log.info("去重后有效数据{}条", validDataList.size());
        
        // 1.3 批量查询现有成绩记录（优化：分批查询避免 IN 子句过长）
        List<Long> studentIds = validDataList.stream()
            .map(NewExamScoreImportExcelVO::getStudentId)
            .collect(Collectors.toList());
        Map<Long, ExamScoreDO> existingScoresMap = new HashMap<>();
        if (!studentIds.isEmpty()) {
            log.info("开始批量查询{}个学生的现有成绩记录", studentIds.size());
            // 分批查询，每批最多 100 个学生ID，避免 IN 子句过长
            int queryBatchSize = 100;
            for (int i = 0; i < studentIds.size(); i += queryBatchSize) {
                int endIndex = Math.min(i + queryBatchSize, studentIds.size());
                List<Long> batchStudentIds = studentIds.subList(i, endIndex);
                List<ExamScoreDO> batchScores = examScoreMapper.selectByExamIdAndStudentIds(examId, batchStudentIds);
                for (ExamScoreDO score : batchScores) {
                    existingScoresMap.put(score.getStudentId(), score);
                }
            }
            log.info("批量查询完成，找到{}条现有成绩记录", existingScoresMap.size());
        }
        
        // 1.4 批量查询班级ID映射
        Set<String> classNames = validDataList.stream()
            .map(NewExamScoreImportExcelVO::getClassName)
            .filter(Objects::nonNull)
            .collect(Collectors.toSet());
        Map<String, Long> classIdMap = batchGetClassIdsByNames(classNames);
        log.info("批量查询到{}个班级的ID映射", classIdMap.size());
        
        // === 第二步：批量准备数据库记录 ===
        
        List<ExamScoreDO> scoresToInsert = new ArrayList<>();
        List<ExamScoreDO> scoresToUpdate = new ArrayList<>();
        List<SubjectScoreDetailDO> allSubjectDetails = new ArrayList<>();
        List<TopScoreRecordDO> allTopScoreRecords = new ArrayList<>();
        
        // 获取当前登录用户的部门ID
        Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
        
        for (NewExamScoreImportExcelVO importData : validDataList) {
            try {
                Long studentId = importData.getStudentId();
                ExamScoreDO existingScore = existingScoresMap.get(studentId);
                boolean isExisting = (existingScore != null);
                
                // 准备成绩记录
                ExamScoreDO examScore;
                if (isExisting) {
                    examScore = existingScore;
                    updateExamScoreFields(examScore, importData, classIdMap, currentUserDeptId, importBatchId);
                    scoresToUpdate.add(examScore);
                    respVO.getUpdateUsernames().add(importData.getStudentName());
                } else {
                    examScore = buildExamScoreFromData(examId, importData, classIdMap, currentUserDeptId, importBatchId);
                    scoresToInsert.add(examScore);
                    respVO.getCreateUsernames().add(importData.getStudentName());
                }
                
                // 准备科目成绩详情
                List<SubjectScoreDetailDO> subjectDetails = buildSubjectScoreDetails(examId, studentId, importData, importBatchId);
                allSubjectDetails.addAll(subjectDetails);
                
                // 准备最高分记录
                List<TopScoreRecordDO> topScoreRecords = buildTopScoreRecords(examId, studentId, importData, importBatchId);
                allTopScoreRecords.addAll(topScoreRecords);
                
            } catch (Exception e) {
                log.error("准备学生{}成绩数据失败", importData.getStudentName(), e);
                String errorMessage = e.getMessage();
                if (errorMessage == null || errorMessage.trim().isEmpty()) {
                    errorMessage = "未知错误: " + e.getClass().getSimpleName();
                }
                respVO.getFailureUsernames().put(importData.getStudentName(), errorMessage);
            }
        }
        
        // === 第三步：批量执行数据库操作 ===
        
        // 3.1 批量插入新成绩记录
        if (!scoresToInsert.isEmpty()) {
            log.info("开始批量插入{}条新成绩记录", scoresToInsert.size());
            examScoreMapper.insertBatch(scoresToInsert);
            log.info("批量插入成绩记录完成");
        }
        
        // 3.2 批量更新现有成绩记录（性能优化：使用批量更新方法）
        if (!scoresToUpdate.isEmpty()) {
            log.info("开始批量更新{}条现有成绩记录", scoresToUpdate.size());
            examScoreMapper.updateBatchById(scoresToUpdate);
            log.info("批量更新成绩记录完成");
        }
        
        // 3.3 删除所有学生的旧科目详情和最高分记录（避免重复和冲突）
        // 注意：这里删除所有学生的旧记录，而不只是更新的学生
        // 原因：即使是新建的成绩记录，也可能已经有科目详情（如果之前导入过）
        List<Long> allStudentIds = new ArrayList<>();
        allStudentIds.addAll(scoresToInsert.stream().map(ExamScoreDO::getStudentId).collect(Collectors.toList()));
        allStudentIds.addAll(scoresToUpdate.stream().map(ExamScoreDO::getStudentId).collect(Collectors.toList()));
        
        if (!allStudentIds.isEmpty()) {
            log.info("开始删除{}个学生的旧科目详情和最高分记录", allStudentIds.size());
            int deletedDetails = subjectScoreDetailMapper.deleteByExamIdAndStudentIds(examId, allStudentIds);
            int deletedTopScores = topScoreRecordMapper.deleteByExamIdAndStudentIds(examId, allStudentIds);
            log.info("删除完成：科目详情{}条，最高分记录{}条", deletedDetails, deletedTopScores);
        }
        
        // 3.4 批量插入科目成绩详情
        if (!allSubjectDetails.isEmpty()) {
            log.info("开始批量插入{}条科目成绩详情", allSubjectDetails.size());
            // 分批插入，每批 500 条，避免 SQL 过长
            int batchSize = 500;
            int totalInserted = 0;
            for (int i = 0; i < allSubjectDetails.size(); i += batchSize) {
                int endIndex = Math.min(i + batchSize, allSubjectDetails.size());
                List<SubjectScoreDetailDO> batch = allSubjectDetails.subList(i, endIndex);
                subjectScoreDetailMapper.insertBatch(batch);
                totalInserted += batch.size();
                log.debug("已插入 {}/{} 条科目成绩详情", totalInserted, allSubjectDetails.size());
            }
            log.info("批量插入科目成绩详情完成，共插入 {} 条", totalInserted);
        }
        
        // 3.5 批量插入最高分记录
        if (!allTopScoreRecords.isEmpty()) {
            log.info("开始批量插入{}条最高分记录", allTopScoreRecords.size());
            // 分批插入，每批 500 条
            int batchSize = 500;
            int totalInserted = 0;
            for (int i = 0; i < allTopScoreRecords.size(); i += batchSize) {
                int endIndex = Math.min(i + batchSize, allTopScoreRecords.size());
                List<TopScoreRecordDO> batch = allTopScoreRecords.subList(i, endIndex);
                topScoreRecordMapper.insertBatch(batch);
                totalInserted += batch.size();
                log.debug("已插入 {}/{} 条最高分记录", totalInserted, allTopScoreRecords.size());
            }
            log.info("批量插入最高分记录完成，共插入 {} 条", totalInserted);
        }
        
        long endTime = System.currentTimeMillis();
        log.info("批量处理完成，耗时{}ms，成功创建{}条，更新{}条，失败{}条", 
                endTime - startTime,
                respVO.getCreateUsernames().size(), 
                respVO.getUpdateUsernames().size(), 
                respVO.getFailureUsernames().size());
        
        return respVO;
    }
    
    /**
     * 批量获取班级ID映射
     */
    private Map<String, Long> batchGetClassIdsByNames(Set<String> classNames) {
        if (classNames == null || classNames.isEmpty()) {
            return new HashMap<>();
        }
        
        List<ExamClassDO> classes = examClassMapper.selectByClassNames(new ArrayList<>(classNames));
        return classes.stream()
            .collect(Collectors.toMap(ExamClassDO::getClassName, ExamClassDO::getId, (v1, v2) -> v1));
    }
    
    /**
     * 构建成绩记录（用于新建）
     */
    private ExamScoreDO buildExamScoreFromData(Long examId, NewExamScoreImportExcelVO importData, 
                                                Map<String, Long> classIdMap, Long currentUserDeptId, String importBatchId) {
        ExamScoreDO examScore = new ExamScoreDO();
        examScore.setExamId(examId);
        examScore.setStudentId(importData.getStudentId());
        
        updateExamScoreFields(examScore, importData, classIdMap, currentUserDeptId, importBatchId);
        
        return examScore;
    }
    
    /**
     * 更新成绩记录字段（用于新建和更新）
     */
    private void updateExamScoreFields(ExamScoreDO examScore, NewExamScoreImportExcelVO importData, 
                                        Map<String, Long> classIdMap, Long currentUserDeptId, String importBatchId) {
        examScore.setStudentNo(importData.getExamNo());
        examScore.setStudentName(importData.getStudentName());
        examScore.setClassName(importData.getClassName());
        examScore.setSubjectCombination(importData.getSubjectCombination());
        examScore.setProvince("安徽");
        
        // 设置导入批次ID
        examScore.setImportBatchId(importBatchId);
        
        // 设置班级ID
        Long classId = classIdMap.get(importData.getClassName());
        examScore.setClassId(classId);
        
        // 设置部门ID
        if (currentUserDeptId != null) {
            examScore.setDeptId(currentUserDeptId);
        }
        
        // 解析选科组合
        parseAndSetSubjectCombination(examScore, importData.getSubjectCombination());
        
        // 总分信息
        examScore.setTotalScore(importData.getTotalOriginalScore());
        examScore.setTotalScoreAssigned(importData.getTotalAssignedScore());
        
        // 排名信息
        if (importData.getTotalUnionRank() != null && importData.getTotalUnionRank() > 0) {
            examScore.setTotalUnionRank(importData.getTotalUnionRank());
        }
        if (importData.getSchoolRank() != null && importData.getSchoolRank() > 0) {
            examScore.setSchoolRank(importData.getSchoolRank());
        }
        if (importData.getClassRank() != null && importData.getClassRank() > 0) {
            examScore.setClassRank(importData.getClassRank());
        }
        if (importData.getCombinationRank() != null && importData.getCombinationRank() > 0) {
            examScore.setCombinationRank(importData.getCombinationRank());
        }
        
        // 各科目原始分
        examScore.setChineseScore(importData.getChineseOriginalScore());
        examScore.setMathScore(importData.getMathOriginalScore());
        examScore.setEnglishScore(importData.getEnglishOriginalScore());
        examScore.setPhysicsScore(importData.getPhysicsOriginalScore());
        examScore.setChemistryScore(importData.getChemistryOriginalScore());
        examScore.setBiologyScore(importData.getBiologyOriginalScore());
        examScore.setPoliticsScore(importData.getPoliticsOriginalScore());
        examScore.setHistoryScore(importData.getHistoryOriginalScore());
        examScore.setGeographyScore(importData.getGeographyOriginalScore());
        
        // 小语种成绩
        examScore.setRussianOriginalScore(importData.getRussianOriginalScore());
        examScore.setRussianAssignedScore(importData.getRussianAssignedScore());
        examScore.setRussianGradeLevel(importData.getRussianGradeLevel());
        examScore.setRussianUnionRank(importData.getRussianUnionRank());
        examScore.setRussianSchoolRank(importData.getRussianSchoolRank());
        examScore.setRussianClassRank(importData.getRussianClassRank());
        
        examScore.setJapaneseOriginalScore(importData.getJapaneseOriginalScore());
        examScore.setJapaneseAssignedScore(importData.getJapaneseAssignedScore());
        examScore.setJapaneseGradeLevel(importData.getJapaneseGradeLevel());
        examScore.setJapaneseUnionRank(importData.getJapaneseUnionRank());
        examScore.setJapaneseSchoolRank(importData.getJapaneseSchoolRank());
        examScore.setJapaneseClassRank(importData.getJapaneseClassRank());
        
        examScore.setSpanishOriginalScore(importData.getSpanishOriginalScore());
        examScore.setSpanishAssignedScore(importData.getSpanishAssignedScore());
        examScore.setSpanishGradeLevel(importData.getSpanishGradeLevel());
        examScore.setSpanishUnionRank(importData.getSpanishUnionRank());
        examScore.setSpanishSchoolRank(importData.getSpanishSchoolRank());
        examScore.setSpanishClassRank(importData.getSpanishClassRank());
    }
    
    /**
     * 构建科目成绩详情列表
     */
    private List<SubjectScoreDetailDO> buildSubjectScoreDetails(Long examId, Long studentId, NewExamScoreImportExcelVO importData, String importBatchId) {
        List<SubjectScoreDetailDO> details = new ArrayList<>();
        
        // 语文
        if (importData.getChineseOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "语文", 
                importData.getChineseOriginalScore(), null, null,
                importData.getChineseUnionRank(), importData.getChineseSchoolRank(), importData.getChineseClassRank(), importBatchId));
        }
        
        // 数学
        if (importData.getMathOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "数学", 
                importData.getMathOriginalScore(), null, null,
                importData.getMathUnionRank(), importData.getMathSchoolRank(), importData.getMathClassRank(), importBatchId));
        }
        
        // 英语
        if (importData.getEnglishOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "英语", 
                importData.getEnglishOriginalScore(), null, null,
                importData.getEnglishUnionRank(), importData.getEnglishSchoolRank(), importData.getEnglishClassRank(), importBatchId));
        }
        
        // 物理（必选科目，无赋分）
        if (importData.getPhysicsOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "物理", 
                importData.getPhysicsOriginalScore(), null, null,
                importData.getPhysicsUnionRank(), importData.getPhysicsSchoolRank(), importData.getPhysicsClassRank(), importBatchId));
        }
        
        // 化学
        if (importData.getChemistryOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "化学", 
                importData.getChemistryOriginalScore(), importData.getChemistryAssignedScore(), importData.getChemistryGradeLevel(),
                importData.getChemistryUnionRank(), importData.getChemistrySchoolRank(), importData.getChemistryClassRank(), importBatchId));
        }
        
        // 生物
        if (importData.getBiologyOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "生物", 
                importData.getBiologyOriginalScore(), importData.getBiologyAssignedScore(), importData.getBiologyGradeLevel(),
                importData.getBiologyUnionRank(), importData.getBiologySchoolRank(), importData.getBiologyClassRank(), importBatchId));
        }
        
        // 政治
        if (importData.getPoliticsOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "政治", 
                importData.getPoliticsOriginalScore(), importData.getPoliticsAssignedScore(), importData.getPoliticsGradeLevel(),
                importData.getPoliticsUnionRank(), importData.getPoliticsSchoolRank(), importData.getPoliticsClassRank(), importBatchId));
        }
        
        // 历史
        if (importData.getHistoryOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "历史", 
                importData.getHistoryOriginalScore(), importData.getHistoryAssignedScore(), importData.getHistoryGradeLevel(),
                importData.getHistoryUnionRank(), importData.getHistorySchoolRank(), importData.getHistoryClassRank(), importBatchId));
        }
        
        // 地理
        if (importData.getGeographyOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "地理", 
                importData.getGeographyOriginalScore(), importData.getGeographyAssignedScore(), importData.getGeographyGradeLevel(),
                importData.getGeographyUnionRank(), importData.getGeographySchoolRank(), importData.getGeographyClassRank(), importBatchId));
        }
        
        // 俄语
        if (importData.getRussianOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "俄语", 
                importData.getRussianOriginalScore(), importData.getRussianAssignedScore(), importData.getRussianGradeLevel(),
                importData.getRussianUnionRank(), importData.getRussianSchoolRank(), importData.getRussianClassRank(), importBatchId));
        }
        
        // 日语
        if (importData.getJapaneseOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "日语", 
                importData.getJapaneseOriginalScore(), importData.getJapaneseAssignedScore(), importData.getJapaneseGradeLevel(),
                importData.getJapaneseUnionRank(), importData.getJapaneseSchoolRank(), importData.getJapaneseClassRank(), importBatchId));
        }
        
        // 西班牙语
        if (importData.getSpanishOriginalScore() != null) {
            details.add(buildSubjectDetail(examId, studentId, "西班牙语", 
                importData.getSpanishOriginalScore(), importData.getSpanishAssignedScore(), importData.getSpanishGradeLevel(),
                importData.getSpanishUnionRank(), importData.getSpanishSchoolRank(), importData.getSpanishClassRank(), importBatchId));
        }
        
        return details;
    }
    
    /**
     * 构建单个科目成绩详情
     */
    private SubjectScoreDetailDO buildSubjectDetail(Long examId, Long studentId, String subjectName,
                                                     BigDecimal originalScore, BigDecimal assignedScore, String gradeLevel,
                                                     Integer unionRank, Integer schoolRank, Integer classRank, String importBatchId) {
        SubjectScoreDetailDO detail = new SubjectScoreDetailDO();
        detail.setExamId(examId);
        detail.setStudentId(studentId);
        detail.setSubjectName(subjectName);
        detail.setOriginalScore(originalScore);
        detail.setAssignedScore(assignedScore);
        detail.setGradeLevel(gradeLevel);
        detail.setUnionRank(unionRank);
        detail.setSchoolRank(schoolRank);
        detail.setClassRank(classRank);
        detail.setIsElectiveSubject(assignedScore != null ? 1 : 0);
        detail.setImportBatchId(importBatchId);
        return detail;
    }
    
    /**
     * 构建最高分记录列表
     */
    private List<TopScoreRecordDO> buildTopScoreRecords(Long examId, Long studentId, NewExamScoreImportExcelVO importData, String importBatchId) {
        List<TopScoreRecordDO> records = new ArrayList<>();
        
        // 第一高分科目
        if (importData.getFirstTopSubject() != null && importData.getFirstTopOriginalScore() != null) {
            records.add(buildTopScoreRecord(examId, studentId, 1, 
                importData.getFirstTopSubject(), 
                importData.getFirstTopOriginalScore(), 
                importData.getFirstTopAssignedScore(),
                importData.getFirstTopUnionRank(),
                importData.getFirstTopSchoolRank(), 
                importData.getFirstTopClassRank(),
                importBatchId));
        }
        
        // 第二高分科目
        if (importData.getSecondTopSubject() != null && importData.getSecondTopOriginalScore() != null) {
            records.add(buildTopScoreRecord(examId, studentId, 2, 
                importData.getSecondTopSubject(), 
                importData.getSecondTopOriginalScore(), 
                importData.getSecondTopAssignedScore(),
                importData.getSecondTopUnionRank(),
                importData.getSecondTopSchoolRank(), 
                importData.getSecondTopClassRank(),
                importBatchId));
        }
        
        return records;
    }
    
    /**
     * 构建单个最高分记录
     */
    private TopScoreRecordDO buildTopScoreRecord(Long examId, Long studentId, Integer rank, String subjectName,
                                                  BigDecimal originalScore, BigDecimal assignedScore, 
                                                  Integer unionRank, Integer schoolRank, Integer classRank, String importBatchId) {
        TopScoreRecordDO record = new TopScoreRecordDO();
        record.setExamId(examId);
        record.setStudentId(studentId);
        record.setRank(rank);  // 字段名是 rank，不是 topRank
        record.setSubjectName(subjectName);
        record.setOriginalScore(originalScore);
        record.setAssignedScore(assignedScore);
        record.setUnionRank(unionRank);
        record.setSchoolRank(schoolRank);
        record.setClassRank(classRank);
        record.setImportBatchId(importBatchId);
        return record;
    }
    
    /**
     * 更新班级学生计数
     * 
     * @param classIds 班级ID列表
     */
    private void updateClassStudentCounts(List<Long> classIds) {
        if (classIds == null || classIds.isEmpty()) {
            log.warn("updateClassStudentCounts: classIds 为空，跳过更新");
            return;
        }
        
        log.info("开始更新{}个班级的学生计数", classIds.size());
        int successCount = 0;
        
        for (Long classId : classIds) {
            try {
                // 查询该班级的实际学生数量（已按student_id去重）
                int studentCount = examClassStudentMapper.countByClassId(classId);
                
                // 查询该班级的所有学生记录（用于调试）
                List<ExamClassStudentDO> allStudents = examClassStudentMapper.selectListByClassId(classId);
                int totalRecords = allStudents.size();
                // 仅统计正常状态的记录，用于判断是否存在同一学生的重复正常记录
                long activeRecords = allStudents.stream()
                        .filter(s -> s.getStatus() == 1)
                        .count();
                long uniqueStudents = allStudents.stream()
                        .filter(s -> s.getStatus() == 1) // 只计算正常状态
                        .map(ExamClassStudentDO::getStudentId)
                        .distinct()
                        .count();
                
                // 更新班级记录
                ExamClassDO examClass = examClassMapper.selectById(classId);
                if (examClass != null) {
                    int oldCount = examClass.getStudentCount() != null ? examClass.getStudentCount() : 0;
                    examClass.setStudentCount(studentCount);
                    examClassMapper.updateById(examClass);
                    
                    long inactiveRecords = totalRecords - activeRecords;
                    if (activeRecords != uniqueStudents) {
                        long duplicateActive = activeRecords - uniqueStudents;
                        log.warn("⚠️ 班级 [{}] 存在重复学生记录：正常记录数={}, 唯一学生数={}, 重复数={}, 更新为：{}",
                                examClass.getClassName(), activeRecords, uniqueStudents, duplicateActive, studentCount);
                    } else {
                        if (inactiveRecords > 0) {
                            log.info("ℹ️ 班级 [{}] 存在 {} 条非正常状态记录（未计入统计）", 
                                    examClass.getClassName(), inactiveRecords);
                        }
                        log.info("✅ 更新班级 [{}] 学生计数：{} → {}（正常记录数={}，总记录数={})", 
                                examClass.getClassName(), oldCount, studentCount, activeRecords, totalRecords);
                    }
                    successCount++;
                } else {
                    log.warn("❌ 未找到班级ID={}", classId);
                }
            } catch (Exception e) {
                log.error("❌ 更新班级ID={}的学生计数失败: {}", classId, e.getMessage(), e);
            }
        }
        
        log.info("班级学生计数更新完成：成功 {}/{}", successCount, classIds.size());
    }
    
    /**
     * 异步计算排名
     * 使用 @Async 注解实现异步执行，不阻塞导入流程
     * 
     * @param examId 考试ID
     */
    @org.springframework.scheduling.annotation.Async("taskExecutor")
    public void asyncCalculateRankings(Long examId) {
        try {
            log.info("【异步任务】开始计算考试ID={}的排名", examId);
            long startTime = System.currentTimeMillis();
            
            rankingCalculationService.calculateAllRankings(examId);
            
            long endTime = System.currentTimeMillis();
            log.info("【异步任务】考试ID={}的排名计算完成，耗时{}ms", examId, endTime - startTime);
            
        } catch (Exception e) {
            log.error("【异步任务】考试ID={}的排名计算失败: {}", examId, e.getMessage(), e);
        }
    }
    
    /**
     * 从新高考数据创建成绩记录
     */
    private ExamScoreDO createExamScoreFromNewData(Long examId, NewExamScoreImportExcelVO importData) {
        return createExamScoreFromNewData(examId, importData, DuplicateHandleStrategyEnum.UPDATE);
    }
    
    /**
     * 根据导入数据创建或处理成绩记录（支持重复处理策略）
     * 
     * @param examId 考试ID
     * @param importData 导入数据
     * @param strategy 重复处理策略
     * @return 成绩记录
     */
    private ExamScoreDO createExamScoreFromNewData(Long examId, NewExamScoreImportExcelVO importData, 
                                                   DuplicateHandleStrategyEnum strategy) {
        // 获取学生ID
        Long studentId = getStudentIdFromImportData(importData);
        
        // 检查是否已存在该学生在该考试中的成绩记录
        ExamScoreDO existingScore = examScoreMapper.selectByExamIdAndStudentId(examId, studentId);
        if (existingScore != null) {
            switch (strategy) {
                case SKIP:
                    log.info("学生{}(ID:{})在考试{}中的成绩已存在，跳过处理", 
                            importData.getStudentName(), studentId, examId);
                    return existingScore;
                case UPDATE:
                    log.warn("学生{}(ID:{})在考试{}中的成绩已存在，将更新现有记录", 
                            importData.getStudentName(), studentId, examId);
                    // 更新现有记录
                    updateExistingExamScore(existingScore, importData);
                    return existingScore;
                case ERROR:
                    throw new IllegalStateException(String.format(
                            "学生%s(ID:%d)在考试%d中的成绩已存在，无法重复导入", 
                            importData.getStudentName(), studentId, examId));
                default:
                    log.warn("未知的重复处理策略: {}, 使用默认更新策略", strategy);
                    updateExistingExamScore(existingScore, importData);
                    return existingScore;
            }
        }
        
        ExamScoreDO examScore = new ExamScoreDO();
        examScore.setExamId(examId);
        examScore.setStudentId(studentId);
        
        examScore.setStudentNo(importData.getExamNo());
        examScore.setStudentName(importData.getStudentName());
        examScore.setClassName(importData.getClassName());
        examScore.setSubjectCombination(importData.getSubjectCombination());
        
        // 设置省份为安徽
        examScore.setProvince("安徽");
        
        // 获取并设置班级ID
        Long classId = getClassIdByClassName(importData.getClassName());
        examScore.setClassId(classId);
        
        // 设置当前登录用户的部门ID（用于数据权限控制）
        Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
        if (currentUserDeptId != null) {
            examScore.setDeptId(currentUserDeptId);
            log.debug("设置成绩记录部门ID: {}, 班级: {}, 学生: {}", 
                     currentUserDeptId, importData.getClassName(), importData.getStudentName());
        } else {
            log.warn("当前登录用户未设置部门ID，成绩记录将不设置部门信息");
        }
        
        // 解析选科组合并设置相关字段
        parseAndSetSubjectCombination(examScore, importData.getSubjectCombination());
        
        // 总分信息
        examScore.setTotalScore(importData.getTotalOriginalScore());
        examScore.setTotalScoreAssigned(importData.getTotalAssignedScore());
        
        // 智能设置排名：如果Excel中有有效排名数据就使用，否则留空由排名计算服务处理
        if (importData.getTotalUnionRank() != null && importData.getTotalUnionRank() > 0) {
            examScore.setTotalUnionRank(importData.getTotalUnionRank());
        }
        if (importData.getSchoolRank() != null && importData.getSchoolRank() > 0) {
            examScore.setSchoolRank(importData.getSchoolRank());
        }
        if (importData.getClassRank() != null && importData.getClassRank() > 0) {
            examScore.setClassRank(importData.getClassRank());
        }
        if (importData.getCombinationRank() != null && importData.getCombinationRank() > 0) {
            examScore.setCombinationRank(importData.getCombinationRank());
        }
        
        // 各科目原始分
        examScore.setChineseScore(importData.getChineseOriginalScore());
        examScore.setMathScore(importData.getMathOriginalScore());
        examScore.setEnglishScore(importData.getEnglishOriginalScore());
        examScore.setPhysicsScore(importData.getPhysicsOriginalScore());
        examScore.setChemistryScore(importData.getChemistryOriginalScore());
        examScore.setBiologyScore(importData.getBiologyOriginalScore());
        examScore.setPoliticsScore(importData.getPoliticsOriginalScore());
        examScore.setHistoryScore(importData.getHistoryOriginalScore());
        examScore.setGeographyScore(importData.getGeographyOriginalScore());
        
        // 小语种成绩
        examScore.setRussianOriginalScore(importData.getRussianOriginalScore());
        examScore.setRussianAssignedScore(importData.getRussianAssignedScore());
        examScore.setRussianGradeLevel(importData.getRussianGradeLevel());
        examScore.setRussianUnionRank(importData.getRussianUnionRank());
        examScore.setRussianSchoolRank(importData.getRussianSchoolRank());
        examScore.setRussianClassRank(importData.getRussianClassRank());
        
        examScore.setJapaneseOriginalScore(importData.getJapaneseOriginalScore());
        examScore.setJapaneseAssignedScore(importData.getJapaneseAssignedScore());
        examScore.setJapaneseGradeLevel(importData.getJapaneseGradeLevel());
        examScore.setJapaneseUnionRank(importData.getJapaneseUnionRank());
        examScore.setJapaneseSchoolRank(importData.getJapaneseSchoolRank());
        examScore.setJapaneseClassRank(importData.getJapaneseClassRank());
        
        examScore.setSpanishOriginalScore(importData.getSpanishOriginalScore());
        examScore.setSpanishAssignedScore(importData.getSpanishAssignedScore());
        examScore.setSpanishGradeLevel(importData.getSpanishGradeLevel());
        examScore.setSpanishUnionRank(importData.getSpanishUnionRank());
        examScore.setSpanishSchoolRank(importData.getSpanishSchoolRank());
        examScore.setSpanishClassRank(importData.getSpanishClassRank());
        
        examScoreMapper.insert(examScore);
        return examScore;
    }
    
    /**
     * 更新现有的成绩记录
     * 
     * @param existingScore 现有成绩记录
     * @param importData 导入数据
     */
    private void updateExistingExamScore(ExamScoreDO existingScore, NewExamScoreImportExcelVO importData) {
        // 更新基本信息
        existingScore.setStudentNo(importData.getExamNo());
        existingScore.setStudentName(importData.getStudentName());
        existingScore.setClassName(importData.getClassName());
        existingScore.setSubjectCombination(importData.getSubjectCombination());
        
        // 设置省份为安徽
        existingScore.setProvince("安徽");
        
        // 获取并设置班级ID
        Long classId = getClassIdByClassName(importData.getClassName());
        existingScore.setClassId(classId);
        
        // 设置当前登录用户的部门ID（用于数据权限控制）
        Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
        if (currentUserDeptId != null) {
            existingScore.setDeptId(currentUserDeptId);
            log.debug("更新成绩记录部门ID: {}, 班级: {}, 学生: {}", 
                     currentUserDeptId, importData.getClassName(), importData.getStudentName());
        } else {
            log.warn("当前登录用户未设置部门ID，成绩记录将不设置部门信息");
        }
        
        // 解析选科组合并设置相关字段
        parseAndSetSubjectCombination(existingScore, importData.getSubjectCombination());
        
        // 总分信息
        existingScore.setTotalScore(importData.getTotalOriginalScore());
        existingScore.setTotalScoreAssigned(importData.getTotalAssignedScore());
        
        // 智能设置排名：如果Excel中有有效排名数据就使用，否则留空由排名计算服务处理
        if (importData.getTotalUnionRank() != null && importData.getTotalUnionRank() > 0) {
            existingScore.setTotalUnionRank(importData.getTotalUnionRank());
        }
        if (importData.getSchoolRank() != null && importData.getSchoolRank() > 0) {
            existingScore.setSchoolRank(importData.getSchoolRank());
        }
        if (importData.getClassRank() != null && importData.getClassRank() > 0) {
            existingScore.setClassRank(importData.getClassRank());
        }
        if (importData.getCombinationRank() != null && importData.getCombinationRank() > 0) {
            existingScore.setCombinationRank(importData.getCombinationRank());
        }
        
        // 各科目原始分
        existingScore.setChineseScore(importData.getChineseOriginalScore());
        existingScore.setMathScore(importData.getMathOriginalScore());
        existingScore.setEnglishScore(importData.getEnglishOriginalScore());
        existingScore.setPhysicsScore(importData.getPhysicsOriginalScore());
        existingScore.setChemistryScore(importData.getChemistryOriginalScore());
        existingScore.setBiologyScore(importData.getBiologyOriginalScore());
        existingScore.setPoliticsScore(importData.getPoliticsOriginalScore());
        existingScore.setHistoryScore(importData.getHistoryOriginalScore());
        existingScore.setGeographyScore(importData.getGeographyOriginalScore());
        
        // 小语种成绩
        existingScore.setRussianOriginalScore(importData.getRussianOriginalScore());
        existingScore.setRussianAssignedScore(importData.getRussianAssignedScore());
        existingScore.setRussianGradeLevel(importData.getRussianGradeLevel());
        existingScore.setRussianUnionRank(importData.getRussianUnionRank());
        existingScore.setRussianSchoolRank(importData.getRussianSchoolRank());
        existingScore.setRussianClassRank(importData.getRussianClassRank());
        
        existingScore.setJapaneseOriginalScore(importData.getJapaneseOriginalScore());
        existingScore.setJapaneseAssignedScore(importData.getJapaneseAssignedScore());
        existingScore.setJapaneseGradeLevel(importData.getJapaneseGradeLevel());
        existingScore.setJapaneseUnionRank(importData.getJapaneseUnionRank());
        existingScore.setJapaneseSchoolRank(importData.getJapaneseSchoolRank());
        existingScore.setJapaneseClassRank(importData.getJapaneseClassRank());
        
        existingScore.setSpanishOriginalScore(importData.getSpanishOriginalScore());
        existingScore.setSpanishAssignedScore(importData.getSpanishAssignedScore());
        existingScore.setSpanishGradeLevel(importData.getSpanishGradeLevel());
        existingScore.setSpanishUnionRank(importData.getSpanishUnionRank());
        existingScore.setSpanishSchoolRank(importData.getSpanishSchoolRank());
        existingScore.setSpanishClassRank(importData.getSpanishClassRank());
        
        // 更新数据库记录
        examScoreMapper.updateById(existingScore);
    }
    
    /**
     * 根据班级名称获取班级ID
     * 
     * @param className 班级名称
     * @return 班级ID，如果找不到返回null
     */
    private Long getClassIdByClassName(String className) {
        if (className == null || className.trim().isEmpty()) {
            return null;
        }
        Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
        ExamClassDO examClass = currentUserDeptId != null
                ? examClassMapper.selectByClassNameAndDeptId(className.trim(), currentUserDeptId)
                : examClassMapper.selectByClassName(className.trim());
        return examClass != null ? examClass.getId() : null;
    }
    
    /**
     * 解析选科组合并设置相关字段
     * 
     * @param examScore 考试成绩对象
     * @param subjectCombination 选科组合（如：物化生、史政地等）
     */
    private void parseAndSetSubjectCombination(ExamScoreDO examScore, String subjectCombination) {
        if (subjectCombination == null || subjectCombination.trim().isEmpty()) {
            return;
        }
        
        String combination = subjectCombination.trim();
        
        // 判断考试类型和设置首选科目
        if (combination.contains("物理") || combination.contains("物")) {
            examScore.setExamType("物理类");
            examScore.setFirstSubject("物理");
            
            // 设置再选科目（除物理外的其他科目）
            StringBuilder secondSubjects = new StringBuilder();
            if (combination.contains("化学") || combination.contains("化")) {
                secondSubjects.append("化学");
            }
            if (combination.contains("生物") || combination.contains("生")) {
                if (secondSubjects.length() > 0) {
                    secondSubjects.append(",");
                }
                secondSubjects.append("生物");
            }
            if (combination.contains("政治") || combination.contains("政")) {
                if (secondSubjects.length() > 0) {
                    secondSubjects.append(",");
                }
                secondSubjects.append("政治");
            }
            if (combination.contains("地理") || combination.contains("地")) {
                if (secondSubjects.length() > 0) {
                    secondSubjects.append(",");
                }
                secondSubjects.append("地理");
            }
            examScore.setSecondSubject(secondSubjects.toString());
            
        } else if (combination.contains("历史") || combination.contains("史")) {
            examScore.setExamType("历史类");
            examScore.setFirstSubject("历史");
            
            // 设置再选科目（除历史外的其他科目）
            StringBuilder secondSubjects = new StringBuilder();
            if (combination.contains("政治") || combination.contains("政")) {
                secondSubjects.append("政治");
            }
            if (combination.contains("地理") || combination.contains("地")) {
                if (secondSubjects.length() > 0) {
                    secondSubjects.append(",");
                }
                secondSubjects.append("地理");
            }
            if (combination.contains("化学") || combination.contains("化")) {
                if (secondSubjects.length() > 0) {
                    secondSubjects.append(",");
                }
                secondSubjects.append("化学");
            }
            if (combination.contains("生物") || combination.contains("生")) {
                if (secondSubjects.length() > 0) {
                    secondSubjects.append(",");
                }
                secondSubjects.append("生物");
            }
            examScore.setSecondSubject(secondSubjects.toString());
            
        } else {
            // 如果既不包含物理也不包含历史，尝试根据科目组合推断
            if (combination.contains("化") || combination.contains("生")) {
                // 包含理科科目，推断为物理类
                examScore.setExamType("物理类");
                examScore.setFirstSubject("物理");
            } else {
                // 默认为历史类
                examScore.setExamType("历史类");
                examScore.setFirstSubject("历史");
            }
            
            // 设置再选科目
            StringBuilder secondSubjects = new StringBuilder();
            String[] subjects = {"化学", "生物", "政治", "地理"};
            String[] shortSubjects = {"化", "生", "政", "地"};
            
            for (int i = 0; i < subjects.length; i++) {
                if (combination.contains(subjects[i]) || combination.contains(shortSubjects[i])) {
                    if (secondSubjects.length() > 0) {
                        secondSubjects.append(",");
                    }
                    secondSubjects.append(subjects[i]);
                }
            }
            examScore.setSecondSubject(secondSubjects.toString());
        }
        
        log.debug("解析选科组合: {} -> 考试类型: {}, 首选科目: {}, 再选科目: {}", 
                 combination, examScore.getExamType(), examScore.getFirstSubject(), examScore.getSecondSubject());
    }
    
    /**
     * 检查是否需要计算排名
     * 如果存在排名字段为空或无效的记录，则需要重新计算排名
     */
    private boolean needsRankingCalculation(Long examId) {
        List<ExamScoreDO> examScores = examScoreMapper.selectListByExamId(examId);
        
        // 检查是否有任何记录的排名字段为空或无效
        for (ExamScoreDO score : examScores) {
            if (score.getSchoolRank() == null || score.getSchoolRank() <= 0 ||
                score.getClassRank() == null || score.getClassRank() <= 0 ||
                score.getCombinationRank() == null || score.getCombinationRank() <= 0) {
                return true; // 需要计算排名
            }
        }
        
        return false; // 所有排名都已存在且有效，不需要重新计算
    }
    
    /**
     * 从创建结果中获取学生ID
     */
    private Long getStudentIdFromCreationResult(AutoGenerationService.BatchCreationResult creationResult, 
                                               NewExamScoreImportExcelVO importData) {
        // 由于BatchCreationResult只包含统计信息，无法直接获取学生ID映射
        // 直接从数据库中查找学生ID
        return getStudentIdFromImportData(importData);
    }
    
    /**
     * 从导入数据中获取学生ID（备用方法）
     */
    private Long getStudentIdFromImportData(NewExamScoreImportExcelVO importData) {
        // 如果导入数据中已经有studentId，直接使用
        if (importData.getStudentId() != null) {
            log.debug("使用导入数据中的学生ID: {}, 学生姓名: {}", importData.getStudentId(), importData.getStudentName());
            return importData.getStudentId();
        }
        
        // 增加详细的调试信息
        log.debug("开始查找学生ID，学生姓名: {}, 班级: {}", 
                 importData.getStudentName(), importData.getClassName());
        
        Long currentUserDeptId = SecurityFrameworkUtils.getLoginUserDeptId();
        ExamClassDO examClassForLookup = currentUserDeptId != null
                ? examClassMapper.selectByClassNameAndDeptId(importData.getClassName(), currentUserDeptId)
                : examClassMapper.selectByClassName(importData.getClassName());
        Long lookupClassId = importData.getClassId() != null ? importData.getClassId() : (examClassForLookup != null ? examClassForLookup.getId() : null);
        List<ExamClassStudentDO> students = lookupClassId != null
                ? examClassStudentMapper.selectListByStudentNameAndClassId(importData.getStudentName(), lookupClassId)
                : examClassStudentMapper.selectListByStudentNameAndClassName(importData.getStudentName(), importData.getClassName());
        
        log.debug("数据库查询结果：找到{}个匹配的学生记录", students.size());
        
        if (students.isEmpty()) {
            if (lookupClassId != null) {
                log.debug("尝试根据班级ID和学生姓名查找学生: classId={}, 姓名={}", lookupClassId, importData.getStudentName());
                List<ExamClassStudentDO> studentsByClassId = examClassStudentMapper.selectListByStudentNameAndClassId(importData.getStudentName(), lookupClassId);
                if (!studentsByClassId.isEmpty()) {
                    students = studentsByClassId;
                    if (students.size() == 1) {
                        ExamClassStudentDO student = students.get(0);
                        log.debug("通过班级ID和姓名找到唯一学生，学生ID: {}, 姓名: {}, 班级ID: {}",
                                student.getStudentId(), student.getStudentName(), lookupClassId);
                        return student.getStudentId();
                    }
                    log.error("发现{}个同名学生但无法精确匹配！学生姓名: {}, 班级ID: {}",
                            students.size(), importData.getStudentName(), lookupClassId);
                    for (int i = 0; i < students.size(); i++) {
                        ExamClassStudentDO s = students.get(i);
                        log.error("  学生{}: ID={}, 姓名={}, 班级={}, 学号={}",
                                i + 1, s.getStudentId(), s.getStudentName(), s.getClassName(), s.getStudentNo());
                    }
                    throw new IllegalStateException(String.format(
                            "发现%d个同名学生无法精确匹配！学生姓名: %s, 班级ID: %s。请检查数据是否重复。",
                            students.size(), importData.getStudentName(), String.valueOf(lookupClassId)));
                }
            }
            
            // 检查班级是否存在
            ExamClassDO examClass = examClassForLookup;
            if (examClass == null) {
                log.error("班级不存在，班级名称: {}", importData.getClassName());
            } else {
                log.debug("班级存在，班级ID: {}, 班级名称: {}", examClass.getId(), examClass.getClassName());
            }
            
            // 如果找不到学生，说明学生还没有被创建，这种情况不应该发生
            log.error("无法找到学生ID，学生姓名: {}, 班级: {}", 
                     importData.getStudentName(), importData.getClassName());
            
            throw new IllegalStateException(String.format(
                "无法找到学生ID，学生可能未正确创建。学生姓名: %s, 班级: %s", 
                importData.getStudentName(), importData.getClassName()));
        }
        
        if (students.size() == 1) {
            // 只有一个学生，直接返回
            ExamClassStudentDO student = students.get(0);
            log.debug("找到唯一学生，学生ID: {}, 学生姓名: {}, 班级: {}", 
                     student.getStudentId(), student.getStudentName(), student.getClassName());
            return student.getStudentId();
        }
        
        // 处理同名学生情况：不使用考号进行匹配，直接提示数据异常
        
        // 【修复】如果找到多个同名学生且无法通过考号区分，这是数据异常，应该抛出错误
        // 原因：随意返回第一个学生会导致不同的Excel记录被分配到同一个学生ID，从而违反唯一约束
        log.error("发现{}个同名学生但无法精确匹配！学生姓名: {}, 班级: {}", 
                students.size(), importData.getStudentName(), importData.getClassName());
        
        // 打印所有匹配到的学生信息，帮助排查问题
        for (int i = 0; i < students.size(); i++) {
            ExamClassStudentDO student = students.get(i);
            log.error("  学生{}: ID={}, 姓名={}, 班级={}, 学号={}", 
                    i + 1, student.getStudentId(), student.getStudentName(), 
                    student.getClassName(), student.getStudentNo());
        }
        
        throw new IllegalStateException(String.format(
                "发现%d个同名学生无法精确匹配！学生姓名: %s, 班级: %s。" +
                "请检查：1) 数据库中是否有重复的学生记录；2) Excel中是否缺少班级ID；3) 班级ID是否正确。",
                students.size(), importData.getStudentName(), importData.getClassName()));
    }
    
    /**
     * 保存科目成绩详情
     */
    private void saveSubjectScoreDetails(Long examId, Long studentId, NewExamScoreImportExcelVO importData) {
        // 保存各科目的详细成绩信息，包括原始分、赋分、等级、排名等
        
        // 语文成绩详情
        if (importData.getChineseOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "语文", 
                importData.getChineseOriginalScore(), null, null,
                importData.getChineseUnionRank(), importData.getChineseSchoolRank(), importData.getChineseClassRank());
        }
        
        // 数学成绩详情
        if (importData.getMathOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "数学", 
                importData.getMathOriginalScore(), null, null,
                importData.getMathUnionRank(), importData.getMathSchoolRank(), importData.getMathClassRank());
        }
        
        // 英语成绩详情
        if (importData.getEnglishOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "英语", 
                importData.getEnglishOriginalScore(), null, null,
                importData.getEnglishUnionRank(), importData.getEnglishSchoolRank(), importData.getEnglishClassRank());
        }
        
        // 物理成绩详情
        if (importData.getPhysicsOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "物理", 
                importData.getPhysicsOriginalScore(), null, null,
                importData.getPhysicsUnionRank(), importData.getPhysicsSchoolRank(), importData.getPhysicsClassRank());
        }
        
        // 化学成绩详情（选考科目，有赋分）
        if (importData.getChemistryOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "化学", 
                importData.getChemistryOriginalScore(), importData.getChemistryAssignedScore(), 
                importData.getChemistryGradeLevel(), importData.getChemistryUnionRank(), 
                importData.getChemistrySchoolRank(), importData.getChemistryClassRank());
        }
        
        // 生物成绩详情（选考科目，有赋分）
        if (importData.getBiologyOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "生物", 
                importData.getBiologyOriginalScore(), importData.getBiologyAssignedScore(), 
                importData.getBiologyGradeLevel(), importData.getBiologyUnionRank(), 
                importData.getBiologySchoolRank(), importData.getBiologyClassRank());
        }
        
        // 政治成绩详情（选考科目，有赋分）
        if (importData.getPoliticsOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "政治", 
                importData.getPoliticsOriginalScore(), importData.getPoliticsAssignedScore(), 
                importData.getPoliticsGradeLevel(), importData.getPoliticsUnionRank(), 
                importData.getPoliticsSchoolRank(), importData.getPoliticsClassRank());
        }
        
        // 地理成绩详情（选考科目，有赋分）
        if (importData.getGeographyOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "地理", 
                importData.getGeographyOriginalScore(), importData.getGeographyAssignedScore(), 
                importData.getGeographyGradeLevel(), importData.getGeographyUnionRank(), 
                importData.getGeographySchoolRank(), importData.getGeographyClassRank());
        }
        
        // 历史成绩详情（选考科目，有赋分）
        if (importData.getHistoryOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "历史", 
                importData.getHistoryOriginalScore(), importData.getHistoryAssignedScore(), 
                importData.getHistoryGradeLevel(), importData.getHistoryUnionRank(), 
                importData.getHistorySchoolRank(), importData.getHistoryClassRank());
        }
        
        // 俄语成绩详情（外语小语种）
        if (importData.getRussianOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "俄语", 
                importData.getRussianOriginalScore(), importData.getRussianAssignedScore(), 
                importData.getRussianGradeLevel(), importData.getRussianUnionRank(), 
                importData.getRussianSchoolRank(), importData.getRussianClassRank());
        }
        
        // 日语成绩详情（外语小语种）
        if (importData.getJapaneseOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "日语", 
                importData.getJapaneseOriginalScore(), importData.getJapaneseAssignedScore(), 
                importData.getJapaneseGradeLevel(), importData.getJapaneseUnionRank(), 
                importData.getJapaneseSchoolRank(), importData.getJapaneseClassRank());
        }
        
        // 西班牙语成绩详情（外语小语种）
        if (importData.getSpanishOriginalScore() != null) {
            saveSubjectDetail(examId, studentId, "西班牙语", 
                importData.getSpanishOriginalScore(), importData.getSpanishAssignedScore(), 
                importData.getSpanishGradeLevel(), importData.getSpanishUnionRank(), 
                importData.getSpanishSchoolRank(), importData.getSpanishClassRank());
        }
    }
    
    /**
     * 保存单个科目成绩详情
     */
    private void saveSubjectDetail(Long examId, Long studentId, String subjectName, 
                                  BigDecimal originalScore, BigDecimal assignedScore, String gradeLevel,
                                  Integer unionRank, Integer schoolRank, Integer classRank) {
        try {
            // 创建科目成绩详情记录
            SubjectScoreDetailDO detail = new SubjectScoreDetailDO();
            detail.setExamId(examId);
            detail.setStudentId(studentId);
            detail.setSubjectName(subjectName);
            detail.setOriginalScore(originalScore);
            detail.setAssignedScore(assignedScore);
            detail.setGradeLevel(gradeLevel);
            detail.setUnionRank(unionRank);
            detail.setSchoolRank(schoolRank);
            detail.setClassRank(classRank);
            
            // 判断是否为选考科目
            boolean isElectiveSubject = assignedScore != null;
            detail.setIsElectiveSubject(isElectiveSubject ? 1 : 0);
            
            // 保存到数据库
            subjectScoreDetailMapper.insert(detail);
            
        } catch (Exception e) {
            log.warn("保存科目{}成绩详情失败: {}", subjectName, e.getMessage());
        }
    }
    
    /**
     * 保存最高分记录
     */
    private void saveTopScoreRecord(Long examId, Long studentId, NewExamScoreImportExcelVO importData) {
        // 保存第一高分科目记录
        if (importData.getFirstTopSubject() != null && importData.getFirstTopOriginalScore() != null) {
            saveTopScoreDetail(examId, studentId, 1, 
                importData.getFirstTopSubject(), 
                importData.getFirstTopOriginalScore(), 
                importData.getFirstTopAssignedScore(),
                importData.getFirstTopUnionRank(),
                importData.getFirstTopSchoolRank(), 
                importData.getFirstTopClassRank());
        }
        
        // 保存第二高分科目记录
        if (importData.getSecondTopSubject() != null && importData.getSecondTopOriginalScore() != null) {
            saveTopScoreDetail(examId, studentId, 2, 
                importData.getSecondTopSubject(), 
                importData.getSecondTopOriginalScore(), 
                importData.getSecondTopAssignedScore(),
                importData.getSecondTopUnionRank(),
                importData.getSecondTopSchoolRank(), 
                importData.getSecondTopClassRank());
        }
    }
    
    /**
     * 保存单个最高分记录详情
     */
    private void saveTopScoreDetail(Long examId, Long studentId, Integer rank, String subjectName,
                                   BigDecimal originalScore, BigDecimal assignedScore, 
                                   Integer unionRank, Integer schoolRank, Integer classRank) {
        try {
            // 创建最高分记录
            TopScoreRecordDO record = new TopScoreRecordDO();
            record.setExamId(examId);
            record.setStudentId(studentId);
            record.setRank(rank); // 1表示第一高分，2表示第二高分
            record.setSubjectName(subjectName);
            record.setOriginalScore(originalScore);
            record.setAssignedScore(assignedScore);
            record.setUnionRank(unionRank);
            record.setSchoolRank(schoolRank);
            record.setClassRank(classRank);
            
            // 保存到数据库
            topScoreRecordMapper.insert(record);
            
        } catch (Exception e) {
            log.warn("保存第{}高分科目{}记录失败: {}", rank, subjectName, e.getMessage());
        }
    }
    
    /**
     * 获取单元格字符串值
     */
    private String getCellStringValue(Cell cell) {
        if (cell == null) return null;
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                return String.valueOf((long) cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return null;
        }
    }
    
    /**
     * 获取单元格BigDecimal值
     */
    private BigDecimal getCellBigDecimalValue(Cell cell) {
        if (cell == null) return null;
        
        switch (cell.getCellType()) {
            case NUMERIC:
                return BigDecimal.valueOf(cell.getNumericCellValue());
            case STRING:
                try {
                    String value = cell.getStringCellValue().trim();
                    return value.isEmpty() ? null : new BigDecimal(value);
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }
    
    /**
     * 获取单元格Integer值
     */
    private Integer getCellIntegerValue(Cell cell) {
        if (cell == null) return null;
        
        switch (cell.getCellType()) {
            case NUMERIC:
                return (int) cell.getNumericCellValue();
            case STRING:
                try {
                    String value = cell.getStringCellValue().trim();
                    return value.isEmpty() ? null : Integer.valueOf(value);
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }
    
    /**
     * 考试信息从标题解析结果
     */
    private static class ExamInfoFromTitle {
        private String examName;
        private String examType;
        private String gradeLevel;
        private String semester;
        private String subjectDirection;
        private String examCategory;
        
        // Getters and Setters
        public String getExamName() { return examName; }
        public void setExamName(String examName) { this.examName = examName; }
        
        public String getExamType() { return examType; }
        public void setExamType(String examType) { this.examType = examType; }
        
        public String getGradeLevel() { return gradeLevel; }
        public void setGradeLevel(String gradeLevel) { this.gradeLevel = gradeLevel; }
        
        public String getSemester() { return semester; }
        public void setSemester(String semester) { this.semester = semester; }
        
        public String getSubjectDirection() { return subjectDirection; }
        public void setSubjectDirection(String subjectDirection) { this.subjectDirection = subjectDirection; }
        
        public String getExamCategory() { return examCategory; }
        public void setExamCategory(String examCategory) { this.examCategory = examCategory; }
    }

    private void validateExamScoreForCreate(ExamScoreImportExcelVO importUser) {
        // 这里可以添加更多的校验逻辑
        if (importUser.getStudentId() == null) {
            throw exception(EXAM_SCORE_STUDENT_ID_NOT_EXISTS);
        }
        if (importUser.getTotalScore() == null) {
            throw exception(EXAM_SCORE_TOTAL_SCORE_NOT_EXISTS);
        }
    }

    @Override
    public List<ExamScoreImportExcelVO> getImportTemplate() {
        return examScoreTemplateManager.getTemplateData();
    }

    @Override
    public List<String> getSupportedTemplateSourceTypes() {
        return examScoreTemplateManager.getSupportedSourceTypes();
    }

    @Override
    public boolean isTemplateSourceTypeSupported(String sourceType) {
        return examScoreTemplateManager.isSourceTypeSupported(sourceType);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void calculateRanking(Long examId) {
        // 计算班级排名
        examScoreMapper.updateClassRankByExamId(examId);
        
        // 计算年级排名
        examScoreMapper.updateGradeRankByExamId(examId);
    }

    @Override
    public ExamScoreStatisticsRespVO getExamScoreStatistics(Long examId) {
        ExamScoreMapper.ExamScoreStatisticsVO statistics = examScoreMapper.selectStatisticsByExamId(examId);

        return ExamScoreStatisticsRespVO.builder()
                .totalCount(statistics.getTotalCount())
                .avgScore(statistics.getAvgScore() != null ? new BigDecimal(statistics.getAvgScore().toString()) : null)
                .maxScore(statistics.getMaxScore() != null ? new BigDecimal(statistics.getMaxScore().toString()) : null)
                .minScore(statistics.getMinScore() != null ? new BigDecimal(statistics.getMinScore().toString()) : null)
                .build();
    }

    @Override
    public ExamScoreAnalysisRespVO getExamScoreAnalysis(Long examId) {
        // 获取基础统计信息
        ExamScoreStatisticsRespVO statistics = getExamScoreStatistics(examId);
        
        // 获取分数分布
        List<Map<String, Object>> scoreDistribution = examScoreMapper.selectGradeScoreDistribution(examId);
        
        // 获取年级统计
        Map<String, Object> gradeStats = examScoreMapper.selectGradeStatistics(examId);
        
        return ExamScoreAnalysisRespVO.builder()
                .statistics(statistics)
                .scoreDistribution(scoreDistribution)
                .gradeStatistics(gradeStats)
                .build();
    }

    /**
     * 获取带有关联数据的成绩响应VO
     */
    public ExamScoreRespVO getExamScoreWithRelations(Long id) {
        ExamScoreDO examScore = examScoreMapper.selectById(id);
        if (examScore == null) {
            return null;
        }
        
        ExamTestDO examTest = examTestService.getExamTest(examScore.getExamId());
        ExamClassDO examClass = examClassMapper.selectById(examScore.getClassId());
        
        return ExamScoreConvert.INSTANCE.convert(examScore, examTest, examClass);
    }

    /**
     * 获取带有关联数据的成绩列表
     */
    public List<ExamScoreRespVO> getExamScoreListWithRelations(Collection<Long> ids) {
        List<ExamScoreDO> examScores = examScoreMapper.selectList(ExamScoreDO::getId, ids);
        return convertToRespVOsWithRelations(examScores);
    }

    /**
     * 获取带有关联数据的成绩分页
     */
    public PageResult<ExamScoreRespVO> getExamScorePageWithRelations(ExamScorePageReqVO pageReqVO) {
        PageResult<ExamScoreDO> pageResult = examScoreMapper.selectPage(pageReqVO);
        List<ExamScoreRespVO> respVOs = convertToRespVOsWithRelations(pageResult.getList());
        return new PageResult<>(respVOs, pageResult.getTotal());
    }

    /**
     * 根据考试ID获取带有关联数据的成绩列表
     */
    public List<ExamScoreRespVO> getExamScoreListByExamIdWithRelations(Long examId) {
        List<ExamScoreDO> examScores = examScoreMapper.selectListByExamId(examId);
        return convertToRespVOsWithRelations(examScores);
    }

    /**
     * 根据学生ID获取带有关联数据的成绩列表
     */
    public List<ExamScoreRespVO> getExamScoreListByStudentIdWithRelations(Long studentId) {
        List<ExamScoreDO> examScores = examScoreMapper.selectListByStudentId(studentId);
        return convertToRespVOsWithRelations(examScores);
    }

    /**
     * 转换成绩DO列表为带有关联数据的响应VO列表
     */
    private List<ExamScoreRespVO> convertToRespVOsWithRelations(List<ExamScoreDO> examScores) {
        if (examScores == null || examScores.isEmpty()) {
            return new ArrayList<>();
        }
        
        // 获取所有相关的考试ID和班级ID
        Set<Long> examIds = examScores.stream()
            .map(ExamScoreDO::getExamId)
            .filter(Objects::nonNull)
            .collect(Collectors.toSet());
        
        Set<Long> classIds = examScores.stream()
            .map(ExamScoreDO::getClassId)
            .filter(Objects::nonNull)
            .collect(Collectors.toSet());
        
        // 批量查询考试和班级信息
        List<ExamTestDO> examTests = new ArrayList<>();
        if (!examIds.isEmpty()) {
            examTests = examTestService.getExamTestList(examIds);
        }
        
        List<ExamClassDO> examClasses = new ArrayList<>();
        if (!classIds.isEmpty()) {
            examClasses = examClassMapper.selectList(ExamClassDO::getId, classIds);
        }
        
        // 使用转换器进行转换
        return ExamScoreConvert.INSTANCE.convertListWithRelations(examScores, examTests, examClasses);
    }

    @Override
    public List<ExamScoreEquivalentExportVO> getEquivalentScoreExportList(Long examId) {
        log.info("开始获取考试{}的等效分导出数据", examId);
        
        // 1. 获取考试的所有成绩
        List<ExamScoreDO> examScores = examScoreMapper.selectListByExamId(examId);
        if (examScores == null || examScores.isEmpty()) {
            log.warn("考试{}没有成绩数据", examId);
            return new ArrayList<>();
        }
        
        log.info("考试{}共有{}条成绩记录", examId, examScores.size());
        
        // 2. 获取考试关联的特控线配置（用于获取省控线）
        List<cn.iocoder.yudao.module.system.dal.dataobject.examcontrolline.ExamControlLineDO> controlLines = 
                examControlLineService.getExamControlLineListByExamId(examId);
        
        // 3. 转换为导出VO
        List<ExamScoreEquivalentExportVO> exportList = new ArrayList<>();
        
        for (ExamScoreDO examScore : examScores) {
            ExamScoreEquivalentExportVO exportVO = convertToEquivalentExportVO(examScore, controlLines);
            exportList.add(exportVO);
        }
        
        log.info("考试{}等效分导出数据准备完成，共{}条记录", examId, exportList.size());
        
        return exportList;
    }
    
    /**
     * 转换成绩DO为等效分导出VO
     */
    private ExamScoreEquivalentExportVO convertToEquivalentExportVO(
            ExamScoreDO examScore, 
            List<cn.iocoder.yudao.module.system.dal.dataobject.examcontrolline.ExamControlLineDO> controlLines) {
        
        ExamScoreEquivalentExportVO exportVO = new ExamScoreEquivalentExportVO();
        
        // 设置基础字段
        exportVO.setStudentNo(examScore.getStudentNo());
        exportVO.setStudentName(examScore.getStudentName());
        exportVO.setClassName(examScore.getClassName());
        exportVO.setExamNo(examScore.getExamNo());
        exportVO.setProvince(examScore.getProvince());
        exportVO.setExamType(examScore.getExamType());
        exportVO.setSubjectCombination(examScore.getSubjectCombination());
        exportVO.setTotalScore(examScore.getTotalScore());
        exportVO.setTotalScoreAssigned(examScore.getTotalScoreAssigned());
        
        // 设置各科成绩
        exportVO.setChineseScore(examScore.getChineseScore());
        exportVO.setMathScore(examScore.getMathScore());
        exportVO.setEnglishScore(examScore.getEnglishScore());
        exportVO.setPhysicsScore(examScore.getPhysicsScore());
        exportVO.setChemistryScore(examScore.getChemistryScore());
        exportVO.setBiologyScore(examScore.getBiologyScore());
        exportVO.setPoliticsScore(examScore.getPoliticsScore());
        exportVO.setHistoryScore(examScore.getHistoryScore());
        exportVO.setGeographyScore(examScore.getGeographyScore());
        
        // 设置排名信息
        exportVO.setClassRank(examScore.getClassRank());
        exportVO.setGradeRank(examScore.getGradeRank());
        exportVO.setSchoolRank(examScore.getSchoolRank());
        exportVO.setCombinationRank(examScore.getCombinationRank());
        
        // 计算等效分和等效位次
        try {
            // 优先使用总分赋分，如果没有则使用原始总分
            BigDecimal scoreForCalculation = examScore.getTotalScoreAssigned() != null ? 
                    examScore.getTotalScoreAssigned() : examScore.getTotalScore();
            
            if (scoreForCalculation == null) {
                log.warn("学生{}没有有效的分数，无法计算等效分", examScore.getStudentNo());
                exportVO.setRemark("缺少有效分数");
                return exportVO;
            }
            
            // 确定省份和考试类型
            String province = examScore.getProvince();
            if (province == null || province.trim().isEmpty()) {
                province = "安徽"; // 默认省份
                log.debug("学生{}未设置省份，使用默认省份：{}", examScore.getStudentNo(), province);
            }
            
            String examType = determineExamType(examScore);
            
            // 查找匹配的特控线并获取对应的分数
            BigDecimal controlLineScore = null;
            if (controlLines != null && !controlLines.isEmpty()) {
                cn.iocoder.yudao.module.system.dal.dataobject.examcontrolline.ExamControlLineDO controlLine = controlLines.get(0);
                
                // 根据考试类型选择对应的特控线分数
                if ("物理类".equals(examType)) {
                    controlLineScore = controlLine.getPhysicsScoreLine();
                } else if ("历史类".equals(examType)) {
                    controlLineScore = controlLine.getHistoryScoreLine();
                } else {
                    // 如果无法确定，优先使用物理类特控线
                    controlLineScore = controlLine.getPhysicsScoreLine();
                    if (controlLineScore == null) {
                        controlLineScore = controlLine.getHistoryScoreLine();
                    }
                }
                
                exportVO.setControlLineScore(controlLineScore);
            } else {
                log.debug("未找到考试类型{}的特控线配置", examType);
            }
            
            // 计算2025年和2024年的等效分和等效位次
            List<Integer> years = Arrays.asList(2025, 2024);
            Map<Integer, BigDecimal> provinceControlLines = getProvinceControlLinesByYears(province, examType, years);
            
            // 2025年等效分和等效位次
            BigDecimal provinceControlLine2025 = provinceControlLines.get(2025);
            if (provinceControlLine2025 != null && controlLineScore != null) {
                exportVO.setProvinceControlLine2025(provinceControlLine2025);
                
                BigDecimal equivalentScore2025 = equivalentScoreRankService.calculateEquivalentScore(
                        scoreForCalculation, controlLineScore, provinceControlLine2025);
                exportVO.setEquivalentScore2025(equivalentScore2025);
                
                Integer equivalentRank2025 = equivalentScoreRankService.calculateEquivalentRank(
                        equivalentScore2025, province, 2025, examType);
                exportVO.setEquivalentRank2025(equivalentRank2025);
            } else {
                if (provinceControlLine2025 == null) {
                    log.debug("未找到{}省{}年{}的省控线", province, 2025, examType);
                }
            }
            
            // 2024年等效分和等效位次
            BigDecimal provinceControlLine2024 = provinceControlLines.get(2024);
            if (provinceControlLine2024 != null && controlLineScore != null) {
                exportVO.setProvinceControlLine2024(provinceControlLine2024);
                
                BigDecimal equivalentScore2024 = equivalentScoreRankService.calculateEquivalentScore(
                        scoreForCalculation, controlLineScore, provinceControlLine2024);
                exportVO.setEquivalentScore2024(equivalentScore2024);
                
                Integer equivalentRank2024 = equivalentScoreRankService.calculateEquivalentRank(
                        equivalentScore2024, province, 2024, examType);
                exportVO.setEquivalentRank2024(equivalentRank2024);
            } else {
                if (provinceControlLine2024 == null) {
                    log.debug("未找到{}省{}年{}的省控线", province, 2024, examType);
                }
            }
            
        } catch (Exception e) {
            log.error("计算学生{}的等效分失败", examScore.getStudentNo(), e);
            exportVO.setRemark("等效分计算失败: " + e.getMessage());
        }
        
        return exportVO;
    }
    
    /**
     * 确定考试类型（物理类/历史类）
     */
    private String determineExamType(ExamScoreDO examScore) {
        // 优先使用成绩记录中的考试类型字段
        if (examScore.getExamType() != null && !examScore.getExamType().trim().isEmpty()) {
            return examScore.getExamType();
        }
        
        // 根据首选科目判断
        if (examScore.getFirstSubject() != null) {
            if ("物理".equals(examScore.getFirstSubject())) {
                return "物理类";
            } else if ("历史".equals(examScore.getFirstSubject())) {
                return "历史类";
            }
        }
        
        // 根据成绩判断
        if (examScore.getPhysicsScore() != null && examScore.getPhysicsScore().compareTo(BigDecimal.ZERO) > 0) {
            return "物理类";
        } else if (examScore.getHistoryScore() != null && examScore.getHistoryScore().compareTo(BigDecimal.ZERO) > 0) {
            return "历史类";
        }
        
        // 默认返回物理类
        return "物理类";
    }
    
    
    /**
     * 获取多个年份的省控线数据
     */
    private Map<Integer, BigDecimal> getProvinceControlLinesByYears(String province, String examType, List<Integer> years) {
        Map<Integer, BigDecimal> result = new HashMap<>();
        
        for (Integer year : years) {
            try {
                // 查询省控线数据，这里查找特殊类型招生控制线
                List<cn.iocoder.yudao.module.system.dal.dataobject.gugu.ProvinceScoreDO> provinceScores = 
                        provinceScoreService.getProvinceScoreList(province, year, examType);
                
                // 查找特殊类型招生控制线（相当于特控线）
                Optional<cn.iocoder.yudao.module.system.dal.dataobject.gugu.ProvinceScoreDO> controlLineScore = 
                        provinceScores.stream()
                        .filter(score -> "特殊类型招生控制线".equals(score.getScoreBatch()) || 
                                       "特殊".equals(score.getScoreBatch()))
                        .findFirst();
                
                if (controlLineScore.isPresent()) {
                    result.put(year, BigDecimal.valueOf(controlLineScore.get().getScore()));
                } else {
                    log.debug("未找到{}省{}年{}的特殊类型招生控制线", province, year, examType);
                }
            } catch (Exception e) {
                log.error("获取{}省{}年{}的省控线失败", province, year, examType, e);
            }
        }
        
        return result;
    }

}
